Weird Question About Querying with Dates

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

-- datetime data type
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

declare @DateVal datetime;
set dateformat mdy;
set @DateVal = '2013-14-02';
select @DateVal;
go
declare @DateVal datetime;
set dateformat dmy;
set @DateVal = '2013-14-02';
select @DateVal;
go

-- SQL Server 2008 and newer, Date and Datetime2 data types
declare @DateVal date;
set dateformat mdy;
set @DateVal = '2013-02-14';
select @DateVal;
go
declare @DateVal datetime2;
set dateformat dmy;
set @DateVal = '2013-02-14';
select @DateVal;
go
declare @DateVal datetime2;
set dateformat mdy;
set @DateVal = '2013-02-14';
select @DateVal;
go
declare @DateVal datetime2;
set dateformat dmy;
set @DateVal = '2013-02-14';
select @DateVal;
go

declare @DateVal date;
set dateformat mdy;
set @DateVal = '2013-14-02';
select @DateVal;
go
declare @DateVal datetime2;
set dateformat dmy;
set @DateVal = '2013-14-02';
select @DateVal;
go
declare @DateVal datetime2;
set dateformat mdy;
set @DateVal = '2013-14-02';
select @DateVal;
go
declare @DateVal datetime2;
set dateformat dmy;
set @DateVal = '2013-14-02';
select @DateVal;
go

declare @DateVal datetime;
set dateformat mdy;
set @DateVal = '2013-02-14T00:00:00';
select @DateVal;
go
declare @DateVal datetime;
set dateformat dmy;
set @DateVal = '2013-02-14T00:00:00';
select @DateVal;
go