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

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)?

  • What does a query return in SQL Server?
  • Querying data by joining two tables in two database on different servers
  • T-SQL Merging data
  • How To change the column order of An Existing Table in SQL Server 2008
  • Getting ParsingError, InvalidSoapActionHeader on SQL Server SOAP request
  • SSIS: Cannot create an OLE DB accessor. Verify that the column metadata is valid
  • 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?

  • Generating next sequence number in sql server
  • Azure SQL maintenance
  • ExecuteNonQuery() returns -1 when execute the stored procedure
  • How to automatically run a stored procedure on scheduler basis?
  • Group rows by contiguous date ranges for groups of values
  • Number of concurrent Sql Server connections
  • 4 Solutions collect form web for “When storing a datetime in sql server (datetime type), what format does it store it in?”

    Internally, according to SQLDenis, they are stored as two integers

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

    It is not stored as “unix time”

    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.

    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

    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 query to display the date time
    select convert (varchar(max),doj,103)as doj from tbl_registration

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.