Anatoly Lubarsky Logo
programming, design, integration, games, music

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:

Tuesday, May 13, 2008 7:13 PM

Comments

# MSSQL 2008: Spatial Data Types - Geography and Geometry
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 [ ... ]

5/14/2008 5:28 PM by Anatoly Lubarsky

# re: MSSQL 2008: What is DATETIME2
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.)

9/16/2008 10:25 PM by Matt

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(02) January, 2018
(01) June, 2013
(03) March, 2013
(02) February, 2013
(01) July, 2012
(01) April, 2012
(01) September, 2011
(01) August, 2011
(03) May, 2011
(01) March, 2011
(02) December, 2010
(01) November, 2010
(01) October, 2010
(01) June, 2010
(01) May, 2010
(02) March, 2010
(01) January, 2010
(02) December, 2009
(03) September, 2009
(03) August, 2009
(09) July, 2009
(04) June, 2009
(03) May, 2009
(02) April, 2009
(03) March, 2009
(02) February, 2009
(02) January, 2009
(05) December, 2008
(04) November, 2008
(06) October, 2008
(04) September, 2008
(07) August, 2008
(04) July, 2008
(05) June, 2008
(07) May, 2008
(05) April, 2008
(03) March, 2008
(02) February, 2008
(03) January, 2008
(03) December, 2007
(05) November, 2007
(05) October, 2007
(07) September, 2007
(13) August, 2007
(11) July, 2007
(14) June, 2007
(16) May, 2007
(14) April, 2007
(10) March, 2007
(14) February, 2007
(20) January, 2007
(15) December, 2006
(12) November, 2006
(11) October, 2006
(13) September, 2006
(13) August, 2006
(12) July, 2006
(11) June, 2006
(12) May, 2006
(22) April, 2006
(30) March, 2006
(13) February, 2006
(14) January, 2006
(19) December, 2005
(17) November, 2005
(17) October, 2005
(17) September, 2005
(12) August, 2005
(14) July, 2005
(09) June, 2005
(15) May, 2005
(12) April, 2005
(21) March, 2005
(11) February, 2005
(12) January, 2005
(19) December, 2004
(13) November, 2004
(12) October, 2004
(15) September, 2004
(09) August, 2004
(25) July, 2004
(23) June, 2004
(31) May, 2004
(21) April, 2004
(16) March, 2004
(09) February, 2004
(06) January, 2004
(02) December, 2003
(01) November, 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs