Post reply

Syntax error converting datetime from character string.？

  • March 18, 2008 at 7:42 pm

    #69996

    i create a table and insert rows

    create table T

    (

    dt varchar(10)

    )

    insert T select null

    insert T select ''

    insert T select '2003-07-01'

    insert T select '2004-01-05'

    insert T select 'SDS'

    --select, no errors

    select dt,Days

    from (select dt,datediff(dd,'2003-07-01',dt) as Days from T where ISDate(dt)=1) Temp_A

    --select, error

    select dt,Days

    from (select dt,datediff(dd,'2003-07-01',dt) as Days from T where ISDate(dt)=1) Temp_A

    where Days> =0

    Syntax error converting datetime from character string.

    why?

  • March 18, 2008 at 11:15 pm

    #791283

    Man, you've got me... never saw such a thing.

    But, riddle me this... why do you want the derived table when the query works fine without it?

    select dt,datediff(dd,'2003-07-01',dt) as Days from T where ISDate(dt)=1

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 19, 2008 at 12:06 am

    #791287

    Looks like a bug to me.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • March 19, 2008 at 12:07 am

    #791289

    ... and it also returns the correct resultset before giving the error.

  • March 19, 2008 at 12:02 pm

    #791622

    Huh. Good point Gogula (great name, my compliments to your parents). I was in grid mode and didn't notice it.

    Looking through the optimizer, it appeared to me that it was hoisting the inner query to the outer, but forgetting to apply the correct conversion in the test.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • March 19, 2008 at 3:30 pm

    #791777

    rbarryyoung (3/19/2008)

    Huh. Good point Gogula (great name, my compliments to your parents).

    I wonder how Jeff got himself responding to somebody with a nickname like YOURS.

    😀

    Look at his signature!

    :hehe:

    _____________
    Code for TallyGenerator

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply