Date conversion

• bledu

  SSChampion

  Points: 12335

  More actions

  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.
  

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  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"

• bledu

  SSChampion

  Points: 12335

  More actions

  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.