TSQL: work with yyyy-mm-dd date format in British English

If you work with SQL Server that is set to use UK (British English) format you may experience a strange behavior: when you try to assign a date that is in ‘yyyy-mm-dd’ format to a DateTime variable or insert it into a DateTime column – an error is thrown

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

or similar. Or even worse – no error is thrown, but an incorrect date is inserted. This is happening because even though ‘yyyy-mm-dd’ looks like universal ISO format, a UK SQL Server interprets it as ‘yyyy-dd-mm’. So a date like ‘2011-07-21’ will throw the above error, but the date ‘2011-08-01’ will be stored as ‘January 8, 2001’ instead of expected ‘August 1, 2011’.

To alleviate this problem simple add a time part to your date – make the format ‘yyyy-mm-ddThh:mm:ss’. So the dates above will become ‘2011-07-21T00:00:00’ and ‘2011-08-01T00:00:00’ and be interpreted correctly by SQL Sercer

2 replies on “TSQL: work with yyyy-mm-dd date format in British English”

  1. @Madhivanan thanks for the tip – very cool. And an interesting article on unambiguous date formats as well.

Leave a Reply

Your email address will not be published. Required fields are marked *