10

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?

codecompleting
  • 9,251
  • 13
  • 61
  • 102
  • Not an answer but a general tip. Cast as binary will help diagnose curiosities like this and others(unicode). select cast(getdate() as binary); – jason saldo Jul 11 '11 at 21:22

4 Answers4

13

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

Matthew
  • 10,244
  • 5
  • 49
  • 104
7

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.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 2
    +1 They may be treated as floating points and manipulated in SQL as if they were, but when they are written to disk they are ints. – Matthew Jul 11 '11 at 21:26
  • They are stored as 2 ints, see my post here http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-are-dates-stored-in-sql-server – SQLMenace Jul 11 '11 at 21:59
0

Here is the code to view how it is stored:

DECLARE @now datetime, @bytenow binary(8)
SELECT @now = getdate()
SELECT @bytenow = convert(binary(8), @now)
SELECT @now AS fulldate, @bytenow AS internal,
       convert(int, substring(@bytenow, 1, 4)) AS days,
       convert(int, substring(@bytenow, 5, 8)) AS ticks

This shows the 2 values:

  1. the first 4 bytes is a number representing days since 1-1-1900
  2. the second 4 bytes is the number of ticks since midnight where one second has 300 ticks.

.

fulldate                internal           days        ticks
----------------------- ------------------ ----------- -----------
2016-11-07 10:54:13.080 0x0000A6B700B3AFB4 42679       11775924

Reference: Solving the Datetime Mystery see figure 3

Robert Brooker
  • 2,148
  • 24
  • 22
-1
DateTime doj = new DateTime();

System.Globalization.CultureInfo c1=new System.Globalization.CultureInfo("en-GB",true);
doj = DateTime.Parse(Txtdoj.Text.Trim(), c1, System.Globalization.DateTimeStyles.NoCurrentDateDefault);

use ..it

use query to display the date time select convert (varchar(max),doj,103)as doj from tbl_registration

vijay
  • 1
  • 1