Eric M Russell (2/18/2013)
SQL Server doesn't store date/times values in a format, it's just a typed integer.However, SET DATEFORMAT setting influences how date/time values are displayed in a resultset and how strings are converted to date/time values. Also, when using the DateTimeOffset datatype, time zone offset comes into play.
When comparing a date/time value to a range, I'd reccomend doing it like this rather than doing a BETWEEN:
WHERE DateColumn >= '2012-08-01' AND DateColumn < '2013-02-14'
This insures that you're looking at only date and the time portion is ingored. Also, the string format YYYY-MM-DD is ISO standard and always translates properly regardless of local language or DATEFORMAT setting.
Not true:
Also, the string format YYYY-MM-DD is ISO standard and always translates properly regardless of local language or DATEFORMAT setting.
The 'YYYY-MM-DD' string format is affected by the setting of DATEFORMAT. Using 'YYYYMMDD' is not affected by the setting of DATEFORMAT.