Lynn Pettis (2/18/2013)

---

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