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
etcIn this series of blog posts, I would explain them with examples
These example are for versions prior to 2008 Internal StorageMany 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 exampledeclare @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_millisecondsRead more: Beyond Relational Part 1, Part 2
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
etcIn this series of blog posts, I would explain them with examples
These example are for versions prior to 2008 Internal StorageMany 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 exampledeclare @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_millisecondsRead more: Beyond Relational Part 1, Part 2