How to avoid text-to-date conversion mismatches

  • Article

 

In one of our previous posts we saw how different set options can affect execution plans and result sets.
Now let's see how to write our code in order to avoid text-to-date conversion mismatches.

In this example we can see one string being converted to two different dates:

 

set language us_english
select cast('11/12/2012' as datetime)

Result: 2012-11-12 00:00:00.000 -> November 12th 2012
 
set language polski
select cast('11/12/2012' as datetime)

Result: 2012-12-11 00:00:00.000 -> December 11th 2012

 

The difference is caused by default dateformat for different languages.
The same problem happens for all date data types: smalldatetime, datetime, date, datetime2, datetimeoffset.

 

The only way to make sure casting is culture independent is to supply date string in one of following formats:

YYYYMMDD without forward or backward slashes or dashes (/ \ -) with or without time
example:

set language us_english
select cast('20121112' as datetime)
Result: 2012-11-12 00:00:00.000 -> November 12th 2012

set language polski
select cast('20121112' as datetime)
Result: 2012-11-12 00:00:00.000 -> November 12th 2012

YYYY-MM-DDThh:mi:ss without spaces
example:

set language us_english
select cast('2012-11-12T00:00:00' as datetime)
Result: 2012-11-12 00:00:00.000 -> November 12th 2012

set language polski
select cast('2012-11-12T00:00:00' as datetime)
Result: 2012-11-12 00:00:00.000 -> November 12th 2012

 

My personal favorite is the first one as it look much more readable and doesn't have to include time.