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
Read more: Beyond Relational Part 1, Part 2
Posts
