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.
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.
Tuesday, May 13, 2008 7:13 PM