Eric M Russell (2/18/2013)
Lynn Pettis (2/18/2013)
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.
I can see where YYYY-MM-DD is affacted by SET LANGUAGE, but I'm not seeing it affacted by SET DATEFORMAT. You're right that YYYYMMDD would be the best for all locations and environments.
Run this:
declare @DateVal datetime;
set dateformat mdy;
set @DateVal = '2013-02-14';
select @DateVal;
go
declare @DateVal datetime;
set dateformat dmy;
set @DateVal = '2013-02-14';
select @DateVal;
go