Have you noticed how date conversions can seem a bit arbitrary at times? How a string value is translated to a date depends on a number of factors, including how you perform the conversion and what language settings you have set for your connection. But there are ways to limit your conversion headaches.

Factors that affect conversion

The SET DATEFORMAT parameter affects how character values are converted to date values. This setting provides a hint of how dates are formatted in character strings. Valid arguments are all permutations of y (year), m (month) and d (day), i.e. mdy, dmy, ymd, ydm, myd, and dym.

SET DATEFORMAT ymd

Note that SET LANGUAGE implicitly (re)sets the DATEFORMAT setting.

Try the following examples to see how character values are converted differently depending on language/dateformat settings:

SET language swedish;
SELECT CAST('10-11-12' AS datetime),
    CAST('2010-11-12' AS datetime),
    CAST('10-11-2012' AS datetime);
SET language us_english;
SELECT CAST('10-11-12' AS datetime),
    CAST('2010-11-12' AS datetime),
    CAST('10-11-2012' AS datetime);

SET language german;
SELECT CAST('10-11-12' AS datetime),
    CAST('2010-11-12' AS datetime),
    CAST('10-11-2012' AS datetime);

Cut-off years

Cut-off years are used when interpreting two-digit years in SQL Server. If the cut-off date is set to 2049 (the SQL Server default, unless you change it), the two-digit years up to 49 will be interpreted as years leading up to 2049, whereas 50, 51, etc will be interpreted as 1950, 1951 and forward.

CAST or CONVERT?

The use of CAST() or CONVERT() to convert string values to dates is a matter of personal preference, but using CONVERT() actually gives you an advantage: With CONVERT(), you can explicitly specify the date format. Example:

SELECT CONVERT(datetime, '10/11/12', 1) AS british_dmy,
       CONVERT(datetime, '10/11/12', 3) AS us_mdy;

A complete list of all the settings can be found on MSDN.

Best practice: Native date syntax

The native date/time syntax in T-SQL is definitely my favourite way of approaching the conversion issue – pretty much because it eliminates the conversion issue entirely. The date is evaluated using a standard format, yyyy-mm-dd or yyyy-mm-dd hh-mm-ss:mmm respectively. There is actually no implicit or explicit conversion taking place, but rather the value is of the datetime type from the start.

SELECT {d '2010-11-12'},
       {ts '2010-11-12 23:59:59.987'};

Whenever you enter dates directly in your T-SQL code, make sure to use this native syntax, and you may never have to mess around with date conversion again.