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.

SNo DATETIME DATETIME2
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.
Ex SELECT GETDATE()+1, GETDATE()-1
Addition or subtraction to numbers is not directly allowed. The function DATEADD should be used
Ex
SELECT DATEADD(DAY,1,SYSDATETIME()),DATEADD(DAY,-1,SYSDATETIME())
4 Milliseconds are rounded to increments of .000, .003, or .007 seconds
Ex
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
Ex
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
Ex
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 DotNetKicks.com
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 www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

 
  Feedback:

comments

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

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions