What is the internal representation of datetime in sql server?

by

It’s stored as an 8 byte field, capable of a range from 1753-01-01 through 9999-12-31, accurate to 0.00333 seconds.

The details are supposedly opaque, but most resources (1), (2) that I’ve found on the web state the following:

The first 4 bytes store the number of days since SQL Server’s epoch (1st Jan 1900) and that the second 4 bytes stores the number of ticks after midnight, where a “tick” is 3.3 milliseconds.

The first four bytes are signed (can be positive or negative), which explains why dates earlier than the epoch can be represented.

More Related Contents:

  1. SQL query to select dates between two dates
  2. Add a column with a default value to an existing table in SQL Server
  3. How to update two tables in one statement in SQL Server 2005?
  4. INSERT statement conflicted with the FOREIGN KEY constraint – SQL Server
  5. How can I convert bigint (UNIX timestamp) to datetime in SQL Server?
  6. How to return multiple values in one column (T-SQL)?
  7. SQL – Subtracting a depleting value from rows
  8. Pass table as parameter into sql server UDF
  9. SQL Server Convert Varchar to Datetime
  10. How Can I use “Date” Datatype in sql server?
  11. Can a Check constraint relate to another table?
  12. SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
  13. How to Replace Multiple Characters in SQL?
  14. EXISTS vs JOIN and use of EXISTS clause
  15. SQL comma-separated row with Group By clause
  16. How to copy a huge table data into another table in SQL Server
  17. How to split string using delimiter char using T-SQL?
  18. Should I use SQL_Variant data type?
  19. How to compare datetime with only date in SQL Server
  20. SQL server select distinct rows using most recent value only
  21. Script to save varbinary data to disk
  22. Measuring Query Performance : “Execution Plan Query Cost” vs “Time Taken”
  23. How to select date without time in SQL
  24. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  25. How do I find the data directory for a SQL Server instance?
  26. Add a row number to result set of a SQL query
  27. How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?
  28. Sql Server : How to use an aggregate function like MAX in a WHERE clause
  29. Backup SQL Schema Only?
  30. Hierarchical Queries in SQL Server 2005

Leave a Comment