Professional Web Applications Themes

Odd Datetime Query Error - Microsoft SQL / MS SQL Server

I am writing a simple sql statement to filter records based on a input date. The date is currently stored in a database as a varchar field, and changing this field to a datetime is not (currently) an option. This sql statement works fine without the WHERE clause, but with the where clause it begins to show records (in Query yzer) and then throws an error when it hits a specific record with a datetime value of 2027-06-30 00:00:00.000 as the date that it is comparing to: here is my query: select property, lease_end from (select property, cast(lease_end_date as datetime) ...

  1. #1

    Default Odd Datetime Query Error

    I am writing a simple sql statement to filter records based on a input date.
    The date is currently stored in a database as a varchar field, and changing
    this field to a datetime is not (currently) an option. This sql statement
    works fine without the WHERE clause, but with the where clause it begins to
    show records (in Query yzer) and then throws an error when it hits a
    specific record with a datetime value of 2027-06-30 00:00:00.000 as the date
    that it is comparing to:

    here is my query:

    select property, lease_end from
    (select property, cast(lease_end_date as datetime) lease_end
    from property where isdate(lease_end_date) = 1) prop
    where lease_end > '1/1/2005'

    this is the error that eventuall pops up (Sql yzer):
    Server: Msg 241, Level 16, State 1, Line 1
    Syntax error converting datetime from character string.

    I am not understanding why this would error out as my derived table is
    (supposedly) filtering out any of the varchar records that are not valid
    dates and returning the column as a datetime before I perform the date
    comparison to it in the final where clause. Any help would be great.
    Thanks.




    nfalconer Guest

  2. #2

    Default Re: Odd Datetime Query Error

    You might take a look at the query plan to see if for
    some strange reason both the isdate() and cast()
    expressions are being evaluated together.



    But since there is nothing unusual about 2027-06-30, assuming
    your dateformat setting is

    Unfortunately for you, the too-clever optimizer decides it
    can run the query faster if it restricts rows from property
    based on the > filter before applying the isdate() filter.

    SQL is a descriptive language, and while your query describes
    a logical way to proceed with obtaining results, the optimizer
    is free to get them in another fashion.

    The solutions that I can think (aside from using datetime,
    which is the best one) are these:

    Change the derived table to
    (select top 100 percent ... order by <choose your clustered index
    columns for speed>)

    This forces the derived table to be completely materialized
    before the outer filter is applied. And in fact, just in case
    someone decides to drop instances of top 100 percent for
    efficiency sake, you might use top 99.99999999999999
    percent, which works, although it's undoented (there you
    risk someone fixing things so the 99.999... is truncated to an int
    instead of used as is).

    You can also change the final condition to:

    where
    case when isdate(lease_end) = 0 then 0
    when lease_end > '1/1/2005' then 1
    end = 1

    Either of these may slow things down, but they should avoid
    the error.

    I also recommend using the format 'YYYYMMDD'
    instead of a language or region specific format in the
    query: ... > '20050101'

    -- Steve Kass
    -- Drew University
    -- Ref: BF3CE58D-C663-41E7-88F5-0E0528DAEA8B

    nfalconer wrote:
     

    Steve Guest

  3. #3

    Default Re: Odd Datetime Query Error

    Good call. The 'top 100 percent' change worked. I'll take the other advice
    into consideration as well. Thanks.


    "Steve Kass" <edu> wrote in message
    news:edu... [/ref]
    te. [/ref]
    changing [/ref]
    statement [/ref]
    to [/ref]
    date 
    >[/ref]


    nfalconer Guest

Similar Threads

  1. Arithmetic overflow error converting expression to datatype datetime
    By dgiet in forum Coldfusion Database Access
    Replies: 2
    Last Post: January 8th, 10:24 PM
  2. # in string/datetime SQL query
    By Eric Bobo in forum ASP Database
    Replies: 0
    Last Post: August 22nd, 03:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139