Understanding Datetime column

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

Read more: Beyond Relational Part 1, Part 2

Posted via email from .NET Info