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

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