DATETIME2 vs DATETIME in SQL Server

In this article, we will compare DATETIME2 vs DATETIME in SQL Server, discuss about their precision and see some examples.

 

About DATETIME2 in SQL Server

The DATETIME2 is a data type in SQL Server, that was originally included 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.

 

Concluding Remarks

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

 

 

Strengthen you SQL Server Development Skills – Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers(special limited-time discount included in link).

Via the course, you will sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

 

Did you find this article useful and interesting? Find hundreds of useful SQL Server programming/development articles in my eBook: “Developing with SQL Server (Second Edition“.

Check our other related SQL Server Development articles.

Check out our latest software releases!

Subscribe to our newsletter and stay up to date!

 

Featured Online Courses:

 

Check our Related SQL Server Development Articles:

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

1 thought on “DATETIME2 vs DATETIME in SQL Server”

Comments are closed.