The Basics of Dates

On my old notes, I wrote down some of the similarities and differences between smalldatetime and datetime data types. Now I'll write up some more of the basics that must be considered before using these data types.
Default Values
Both these data types use the base date (January 1, 1900) as their default value. Even if Datetime can handle much earlier dates, it will always default to the base date.

select cast('' as smalldatetime) as 'smalldatetime',
cast('' as datetime) as 'datetime'

Here is the result set:

smalldatetime           datetime
----------------------- -----------------------
1900-01-01 00:00:00     1900-01-01 00:00:00.000

Both function returned the base date as the default date.

Time, however, will always default to zero (00:00:00). The only difference is the fractional seconds that DateTime can handle.

select
cast('12/29/2008' as smalldatetime) as 'smalldatetime',
cast('12/29/2008' as datetime) as 'datetime'

smalldatetime            datetime
----------------------- -----------------------
2008-12-29 00:00:00     2008-12-29 00:00:00.000

Rounding and Precisions
SmallDateTime has a simple way of preserving its one minute precision. All dates with 29.998 seconds and below will be rounded down to the nearest minute, all dates with 29.999 seconds and higher will be rounded up to the next minute.

select '20091229 23:59:29.998' as 'string',
cast('20091229 23:59:29.998' as smalldatetime) as 'smalldatetime'

string                smalldatetime
--------------------- -----------------------
20091229 23:59:29.998 2009-12-29 23:59:00


select '20091229 23:59:29.999' as 'string',
cast('20091229 23:59:29.999' as smalldatetime) as 'smalldatetime'

string                smalldatetime
--------------------- -----------------------
20091229 23:59:29.999 2009-12-30 00:00:00

DateTime has a more complicated rounding off calculation. Dates are rounded to increments of .000, .003 or .007 seconds.
Consider the following examples:
declare @DateData table(stringdate varchar(25))

set nocount on
insert into @DateData values('20091229 23:59:29.990')
insert into @DateData values('20091229 23:59:29.991')
insert into @DateData values('20091229 23:59:29.992')
insert into @DateData values('20091229 23:59:29.993')
insert into @DateData values('20091229 23:59:29.994')
insert into @DateData values('20091229 23:59:29.995')
insert into @DateData values('20091229 23:59:29.996')
insert into @DateData values('20091229 23:59:29.997')
insert into @DateData values('20091229 23:59:29.998')
insert into @DateData values('20091229 23:59:29.999')

select stringdate, cast(stringdate as datetime) converted
from @DateData

Here's the result set:

stringdate                converted
------------------------- -----------------------
20091229 23:59:29.990     2009-12-29 23:59:29.990
20091229 23:59:29.991     2009-12-29 23:59:29.990
20091229 23:59:29.992     2009-12-29 23:59:29.993
20091229 23:59:29.993     2009-12-29 23:59:29.993
20091229 23:59:29.994     2009-12-29 23:59:29.993
20091229 23:59:29.995     2009-12-29 23:59:29.997
20091229 23:59:29.996     2009-12-29 23:59:29.997
20091229 23:59:29.997     2009-12-29 23:59:29.997
20091229 23:59:29.998     2009-12-29 23:59:29.997
20091229 23:59:29.999     2009-12-29 23:59:30.000

Notice that .995 is rounded to .997 even if it's exactly between .003 and .007. This is because seconds are still integers and will still follow the integer rule of rounding off.

Formats

Whenever a date values is used in T-SQL, it’s probably specified in string literal. However, SQL Server might incorrectly interpret these strings as different dates. A date value '08/12/05' could be interpreted in six different ways. These interpretations are usually affected by the SET DATEFORMAT and SET LANGUAGE settings. There are some string literals that are not affected by these settings. Unless you are sure of these settings, try using a setting-independent format.

Here are some of the acceptable date literal formats:

Numeric Separated	'12/29/2008 14:21:00.000'	DF	LN
Numeric Unseparated	'20081229 14:21:00.000'	-	-
ANSI-SQL	'1998-12-23 14:23:05'	DF	LN
Alphabetic	'29 December 1998 14:23:05'	-	LN
ODBC DateTime	{ts '1998-12-29 14:23:05'}	-	-
ODBC Date	{d '1998-12-29'	-	-
ODBC Time	{t '14:29:09'}	-	-
ISO 8601	'1998-12-29T14:27:09'	-	-
Time	'14:29:09' '2:29:09 PM'	-	-

DF - SET DATEFORMAT dependent
LN - SET LANGUAGE dependent

Notice that ANSI-SQL is also a numeric-separated format, but still LANGUAGE and DATEFORMAT dependent. ODBC uses escape sequences to identify date (d), time (t) and timestamp (date+time). SQL Server always treats ODBC date data as DateTime.

Let's see how DATEFORMAT and LANGUAGE settings can affect your literal string. The code below will give an error:

set dateformat dmy
select cast('12/29/2008' as datetime)

Here's the result:

-----------------------
Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.

This code however, is valid:

set dateformat mdy
select cast('12/29/2008' as datetime)

Here's the result:

-----------------------
2008-12-29 00:00:00.000

Here's how language setting can affect your date literals:

set language British
select datename(month,cast('08/12/05' as datetime))

Here's the result:

Changed language setting to British.

------------------------------
December

And changing the setting :

set language us_english
select datename(month,cast('08/12/05' as datetime))

Can change the result of the same code to:

Changed language setting to us_english.
------------------------------
August

To avoid these errors, try to use those formats that are not dependent to LANGUAGE and DATEFORMAT settings.



~~CK