November 20, 2012

SQL Server: Datetime vs Datetime2 Datatype

The datetime2 datatype was introduced in SQL Server 2008 along with the date and time datatypes.In this post, we will see some differences between the datetime and datetime2 datatypes.

1 Minimum and maximum date ranges are
1900-01-01 and 9999-12-31 23:59:59.997
Minimum and maximum date ranges are
0001-01-01 and 9999-12-31 23:59:59.9999999
2 Returns Current date with time with 3 fractional seconds precision Return Current date with time with 7 fractional seconds precision
3 Addition or subtraction to numbers is directly allowed.
Addition or subtraction to numbers is not directly allowed. The function DATEADD should be used
4 Milliseconds are rounded to increments of .000, .003, or .007 seconds
declare @datetime datetime
select @datetime ='2012-10-19 12:23:45.345'
select @datetime

The result is 2012-10-19 12:23:45.347
Not rounded until the millisecond precision exceeds 7
declare @sysdatetime datetime2(7)
select @sysdatetime ='2012-10-19 12:23:45.99999945'
select @sysdatetime

The result is 2012-10-19 12:23:45.9999995
5 Datatype cannot have variable size Datatype can have variable size
DECLARE @sysdatetime1 datetime2(3),@sysdatetime1 datetime2(5)
6 TIME values can be added directly
declare @datetime datetime
select @datetime ='2012-12-31 12:59:59.99'
select @datetime+'12:00:00'
TIME values cannot be added directly
7 8 bytes is required to store the value Depends on the millisecond precision; 6 to 8 bytes are required to store the value

Did you like this post?
kick it on
subscribe via rss subscribe via e-mail
print this post follow me on twitter

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at and a moderator at His T-sql blog is at



0 Responses to "SQL Server: Datetime vs Datetime2 Datatype"

Copyright © 2009-2016 All Rights Reserved for by Suprotim Agarwal | Terms and Conditions