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
Note that YYYYMMDD will work regardless of local date settings. So you can just remove – from the format
You may find this interesting too http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-ii.aspx
@Madhivanan thanks for the tip – very cool. And an interesting article on unambiguous date formats as well.