When storing a datetime in sql server (datetime type), what format does it store it in?

Question

When storing a datetime value in sql server 2008, using the datetime type, what format will it store the value in?

Is it the number of seconds since 1970 (or whatever)?

On the application side (.net), I am passing in a datetime object.

When I view the table's rows in query analyzer, will it format it according to my culture settings or am I viewing exactly what is stored in the db?

Answer 1

It's stored as a floating point representing days since January 1st, 1900. If you round it down by casting it to an int, that leaves only the date part:

select  cast(cast(getdate() as int) as datetime)
-->
2011-07-12 00:00:00.000

Or if you add a number to it, that's adding a number of days. For example, adding 0.5 is equivalent of adding 12 hours:

select  getdate() + 0.5
-->
2011-07-12 11:22:09.927

See this MSDN article for details.

Datetime does not include format information; query analyzer can display it in any way it likes. It will probably try to honor your client PC's regional settings.

Answer 2

Internally, according to SQLDenis, they are stored as two integers
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-are-dates-stored-in-sql-server

When you view them in the query analyzer you are seeing evaluated numbers.

It is not stored as "unix time"
http://en.wikipedia.org/wiki/Unix_time