Or ‘That’s not the date I wanted?‘
Date time conversions are among the trickiest of conversions in SQL, and the most likely to cause unexpected errors. Mostly, this is because of the variety of formats that dates, when expressed as strings, can be written with. Let’s start with a simple example.
SELECT CAST('20/05/2007' as DATETIME)
Should work?
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
How about this one?
SELECT CAST('07/08/09' AS DATETIME)
Well, that one worked, but the result was somewhat unexpected. The locale on the server I’m using is set to English (South Africa) and the short date format to yy/mm/dd. However, SQL interpreted the date above as 2009-07-08, instead of what would probably be expected, 2007-08-09.
The default date format that SQL assumes when doing a conversion to datetime where the input is of the form xx/yy/zz or xx/yy/zzzz is the US format of mm/dd/yyyy. This is important and is probably one of the greatest causes of incorrect conversions or conversion errors when converting dates from strings.
So, what to do when you do have a date in a format other than the one SQL is expecting and need to cast it to datetime? Fortunately, the convert function provides the answer.
Convert accepts three parameters, the third being optional. The third defines the format for conversions and is most applicable for conversions to and from datetime, though it does have options for money, numeric and xml.
Going back to the initial examples, changing the cast to a convert and adding in the formatting parameter does allow them to be converted properly.
SELECT CONVERT(DATETIME, '20/05/2007',103) -- 2007-05-20 00:00:00.000 SELECT CONVERT(DATETIME, '07/08/09',11) -- 2007-08-09 00:00:00.000
Hence, when converting dates that are stored as strings to datetime, convert is a vastly better option than cast, especially when used with the format option.