Post reply

Date conversion

  • June 27, 2007 at 9:04 am

    #196581

    why does this statement work

    select

    cast('2007/06/20' as datetime)

    and yet this one generates an error

    select

    cast('20/06/2007' as datetime)

     

    Everything you can imagine is real.

  • June 27, 2007 at 9:15 am

    #715565

    It depends on the SET DATEFORMAT setting.

    If unsure, always use ISO date format YYYYMMDD or YYYY-MM-DD.

    In your case, since SQL Server cannot interpret last string as ISO, it tries MDY first, which yields month 20 and day 6 which gives an error.

     

    set

    dateformat dmy

    select

    cast('20070620' as datetime)

    select cast('20-06-2007' as datetime)

     

    N 56°04'39.16"
    E 12°55'05.25"

  • June 27, 2007 at 10:03 am

    #715578

    Thanks Peter that worked a treat.

    I had tried using convert() using 120 but that failed as well. Had been a bit afraid to use the DATEFORMAT thingy because the column has different date formats

    Everything you can imagine is real.

Viewing 3 posts - 1 through 2 (of 2 total)

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