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
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).