There are N number of questions asked in the forums about handling dates in query
Most of the people who ask questions dont understand how datetime column works in SQL Server
Some of the questions frequently asked are about
1 using dates in the WHERE caluse
2 formatting dates using SQL
3 inserting dates to the table with specific date format
etc
In this series of blog posts, I would explain them with examples
These example are for versions prior to 2008
Internal Storage
Many people think that dates are actually stored with specific formats like MM/DD/YYY, DD/MM/YYYY, etc in the table.
Some may think they are stored in YYYY-MM-DD HH:MM:SS format becuase when they select date columns Query analyser display them in such a format
But SQL Server stores datetime values as a two 4-byte integers
First 4-byte for Date value (number of days from base date 1900-01-01)
Second 4-bytes for time value (number of milliseconds after midnight)
Let us see an example
declare @mydate datetime
set @mydate='2009-12-10 18:32:55:873'
select
@mydate as source_date,
datediff(day, '1900-01-01',@mydate) as no_of_days,
convert(char(15),@mydate,114) as time_part,
datediff(millisecond, '1900-01-01',convert(char(15),@mydate,114)) as number_of_milliseconds
The result is
source_date no_of_days time_part number_of_milliseconds
----------------------- ----------- --------------- ----------------------
2009-12-10 18:32:55.873 40155 18:32:55:873 66775873
From the above example it is very clear that datetime values are converted to integers 40155 and 66775873
and stored in the column Now we will see how these integers are converted to datetime
select
dateadd(day,40155, '1900-01-01') as date,
dateadd(millisecond,66775873,dateadd(day,40155, '1900-01-01')) as date_time
The result is
date date_time
----------------------- -----------------------
2009-12-10 00:00:00.000 2009-12-10 18:32:55.873
When you want to retreive dates, SQL Server does a convertion and give date with time value as shown in the above example
Date Ranges
Datetime
Minimum value : January 1, 1753
Maximum value :December 31, 9999
Smalldatetime
Minimum value : January 1, 1900
Maximum value :June 6, 2079
As you see above, any value that is out of that range will lead to error
Rounding
Datetime
Datetime value is rounded to 0.000,0.003 or 0.007 milliseconds
select cast('2010-01-01 12:45:34.755' as datetime)
Result is
dates
-----------------------
2010-01-01 12:45:34.757
Note the millisecond part it is 757 and not 755 as in the original time
We will see how based on the last digit of the millisecond values are rounded
Last Digit Rounded value
---------- -----------------
0 or 1 0
2,3 or 4 3
5,6,7 or 8 7
9 0 ( increase previous digit)
Now run the following code to understand how millisecond roundings happen
select '2010-01-01 12:45:34.750' as original_milliseconds, cast('2010-01-01 12:45:34.750' as datetime) as rounded_milliseconds
union all
select '2010-01-01 12:45:34.751' , cast('2010-01-01 12:45:34.751' as datetime)
union all
select '2010-01-01 12:45:34.752' , cast('2010-01-01 12:45:34.752' as datetime)
union all
select '2010-01-01 12:45:34.753' , cast('2010-01-01 12:45:34.753' as datetime)
union all
select '2010-01-01 12:45:34.754' , cast('2010-01-01 12:45:34.754' as datetime)
union all
select '2010-01-01 12:45:34.755' , cast('2010-01-01 12:45:34.755' as datetime)
union all
select '2010-01-01 12:45:34.756' , cast('2010-01-01 12:45:34.756' as datetime)
union all
select '2010-01-01 12:45:34.757' , cast('2010-01-01 12:45:34.757' as datetime)
union all
select '2010-01-01 12:45:34.758' , cast('2010-01-01 12:45:34.758' as datetime)
union all
select '2010-01-01 12:45:34.759' , cast('2010-01-01 12:45:34.759' as datetime)
The result is
original_milliseconds rounded_milliseconds
----------------------- -----------------------
2010-01-01 12:45:34.750 2010-01-01 12:45:34.750
2010-01-01 12:45:34.751 2010-01-01 12:45:34.750
2010-01-01 12:45:34.752 2010-01-01 12:45:34.753
2010-01-01 12:45:34.753 2010-01-01 12:45:34.753
2010-01-01 12:45:34.754 2010-01-01 12:45:34.753
2010-01-01 12:45:34.755 2010-01-01 12:45:34.757
2010-01-01 12:45:34.756 2010-01-01 12:45:34.757
2010-01-01 12:45:34.757 2010-01-01 12:45:34.757
2010-01-01 12:45:34.758 2010-01-01 12:45:34.757
2010-01-01 12:45:34.759 2010-01-01 12:45:34.760
So the last digit of the millisecond is always 0,3 or 7
SmallDatetime
SmallDatetime value is rounded to 1 minute Values of 29.998 seconds or less are rounded down to the nearest minute; values of 29.999 seconds or more are rounded up to the nearest minute.
The following example explains it
select '2010-01-01 12:45:24.755' as original_smalldatetime, cast('2010-01-01 12:45:24.755' as smalldatetime) as rounded_smalldatetime
union all
select '2010-01-01 12:45:34.755' as original_smalldatetime, cast('2010-01-01 12:45:34.755' as smalldatetime) as rounded_smalldatetime
The result is
original_smalldatetime rounded_smalldatetime
----------------------- -----------------------
2010-01-01 12:45:24.755 2010-01-01 12:45:00
2010-01-01 12:45:34.755 2010-01-01 12:46:00
Millisecond expression
There is a difference in expressing the millisecond if it is less than 3 digits Let us take this example
select
cast('2010-01-01 12:45:34.79' as datetime) as date1,
cast('2010-01-01 12:45:34:79' as datetime) as date2
The result is
date1 date2
----------------------- -----------------------
2010-01-01 12:45:34.790 2010-01-01 12:45:34.080
As you see there is a different millisecond value in the result because
when a dot is used,
1 A single digit denotes tenths-of-a-second
2 Two digits denote hundredths-of-a-second
3 Three digits denote thousandsths-of-a-second
when a colon is used, the number denotes thousandsths-of-a-second
So, in short, if the number of milliseconds is less than 3, Zeroes are added on the right side if a dot is used and Zeroes are added on the left side if a colon is used in order to make it three digits.
So you should be careful when expressing millisecond values
Keep following this series of posts.
Refer Part II that gives more informations about date formats
Refer Part III that gives more informations on how to use date values in the WHERE clasue
Refer Part IV that gives more informations on the best practises when using date values