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