Friday, December 19, 2008

DATETIME2 vs DATETIME in SQL Server 2008

The DATETIME2 is a new data type in SQL Server 2008.
We all know of the existence of the DATETIME.

So, what is the difference between these two data types?

Let's try to compare them with some examples.

--Comparison 1: Notice the date - Both data types work fine
select cast('1753-01-01 18:00:00.123' as DATETIME) as [datetime]
Result (Success): 1753-01-01 18:00:00.123

select cast('1753-01-01 18:00:00.123' as DATETIME2) as [datetime2]
Result (Success): 1753-01-01 18:00:00.1230000

Comments: Please note the precision of the DATETIME2. It provides support up to nanoseconds!


--Comparison 2: Notice the time precision
select cast('1753-01-01 18:00:00.1234' as DATETIME) as [datetime]
Result (Error): Conversion failed when converting date and/or time from character string

select cast('1753-01-01 18:00:00.1234' as DATETIME2) as [datetime2]
Result (Success): 1753-01-01 18:00:00.1234000

Comments: DATΕTIME does not support time precision more than milliseconds and that's why the above conversion fails. Though, the DATETIME2 supports up to nanoseconds and the conversion works.


--Comparison 3: Notice the date values
select cast('1653-01-01 18:00:00.123' as DATETIME) as [datetime]
Result (Error): The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

select cast('1653-01-01 18:00:00.123' as DATETIME2) as [datetime]
Result (Success): 1653-01-01 18:00:00.1230000

Comments: DATΕTIME does not support date values before the year 1753 and that's why the conversion fails. Though, DATETIME2 supports until back to year 0001 and so the conversion works.

Conclusions: The DATETIME2 offers support for larger date ranges and larger time precision.


DATETIME: Date and Time Ranges
------------------------------------
The supported date range is:1753-01-01 through 9999-12-31 (January 1, 1753, AD through December 31, 9999 AD)

The supported time range is: 00:00:00 through 23:59:59.997


DATETIME2: Date and Time Ranges
-------------------------------------
The supported date range is: 0001-01-01 through 9999-12-31 (January 1,1 AD through December 31, 9999 AD)

The Supported time range is: 00:00:00 through 23:59:59.9999999
[Ad]
Easily secure your SQL Server instances! Try out DBA Security Advisor!
DBA Security Advisor for SQL Server by SQLArtBits
DBA Security Advisor was developed by SQLArtBits. The tool was being carefully developed for an entire year and it contains comprehensive security checks and recommendations based on proven SQL Server security best practices. Feel free to download and use the Community Edition which is free and if you find it useful, you can consider upgrading to the Enterprise Edition!

Artemakis Artemiou [MVP]

Author & Editor

Artemakis Artemiou is a Senior SQL Server Architect, Software Developer and Microsoft Data Platform MVP. He is also an author, regular blogger, president of Cyprus.NET User Group and CY Country leader of INETA-EU. He is also the creator of DBA Security Advisor and In-Memory OLTP Simulator. Artemakis is a frequent guest author of worldwide well-respected online journals where he writes articles focusing on many SQL Server topics.

Reference: The SQL Server and .NET Hub (http://www.sqlnethub.com)

1 comments:

Dhinesh said...

It is very nice and easily understandably, Thanks