MSSQL 2008: What is DATETIME2

MSSQL 2008 introduces several new DATETIME datatypes for T-SQL. It is obvious that TIME and DATE provide the ability to store separately DATE and TIME parts of DATETIME. It was not possible to do in previous versions of MSSQL, however it was possible to get either part of the DATETIME using DATETIME functions, for example a couple of years ago I posted about how to Get only Date from DateTime.

• TIME
• DATE
• DATETIME2
• DATETIMEOFFSET

What is DATETIME2 ? It is an improved version of DATETIME. It we compare between them:

• Storage: DATETIME2 takes 6-8 bytes, DATETIME takes 8 bytes.
• Accuracy: DATETIME2 (100 nanosec), DATETIME (333 nanosec).
• Range: DATETIME2 (0001/01/01 - 9999/12/31), DATETIME (1753/01/01 - 9999/12/31).

Sounds cool. Except strange name used for the datatype...(LOL) Seems like folks in Microsoft copied VARCHAR2 convention from Oracle which I didn't understand either.

DATETIMEOFFSET datatype is like DATETIME2 plus in addition it can store TIMEZONE in hh:mm while the actual datetime is stored in UTC. This datatype takes up to 10 bytes in storage however.

EDIT: There are also new system functions introduced: SYSDATETIME(), SYSUTCDATETIME(). Both return system time AS DATETIME2. And also SYSDATETIMEOFFSET() which returns system time as DATETIMEOFFSET.

HTH

Related Posts:

• T-SQL: LIKE Operator and Wildcard Characters
• MSSQL: Search and Replace in a TEXT(NTEXT) column
• T-SQL: Get Age from Date of Birth
• MSSQL 2005 Tip: Using TOP with Subquery
• T-SQL Query Processing Order
• How to Insert DBCC Output into MSSQL Table
• MSSQL: sp_xml_preparedocument is not supported in fibers mode
• MSSQL: Got arithmetic overflow for INT ?
• MSSQL 2005 Express Edition - unbeatable
• T-SQL: The Rozenshtein Method

In addition to new DATETIME data types introduced in MSSQL 2008 there are geography and geometry data types introduced - useful for storing and manipulating geodetic data. Geometry data type is used to store flat map data like points, lines, and shapes [ ... ]

My initial reaction was that datetime2 was a stupid name, and that it should be bigdatetime, but datetime2's storage size isn't bigger...

smalldatetime: 4 bytes.
datetime: 8 bytes.
datetime2: 6 bytes for precisions less than 3; 7 bytes for precisions 4 and 5. All other precisions require 8 bytes.

I presume bigdatetime is reserved for a type that can store BCE dates. (You'd think there would have been enough complaints from archaeologists, historians, and other scientists to get MS to create a BCE datetime type already, but apparently MS is okay with losing a little business to Oracle.)