Difference between DateTime and DateTime2 DataType

Difference between DateTime and DateTime2 DataType

DateTime2 is the new Data Type introduced in Sql Server 2008 for storing Date and Time value. As per MSDN, Microsoft Suggests to use this new Data Type for new work instead of DateTime.

Following table summarizes some of the major difference between this new DateTime2 and the old DateTime Data Type.

	DateTime	DateTime2[(n)]
Min Value	1753-01-01 00:00:00	0001-01-01 00:00:00
Max Value	9999-12-31 23:59:59.997	9999-12-31 23:59:59.9999999
Storage Size	8 Bytes	6 to 8 bytes Note: Parameter n is optional and if it is not specified then fractional seconds precision is 7 digit and it can be from 0 to 7 digit. For fractional seconds precision <3, takes 6 bytes For fractional seconds precision 3 or 4 it will take 7 bytes For fractional seconds precision >4 it will take 8 bytes
Usage	Declare @now datetime	Declare @now datetime2(7)
Compliance	Is not an ANSI/ISO compliant	Is an ANSI/ISO compliant
Current Date and Time function	GetDate() – It returns DB Current Date and Time of DateTime Data Type Example: SELECT GETDATE() Result: 2011-09-16 13:23:18.767	SYSDATETIME()- It returns DB Current Date and Time of DateTime2 Data Type Example:SELECT SYSDATETIME() Result: 2011-09-16 13:23:18.7676720
+/- days	WORKS Example: DECLARE @nowDateTime DATETIME = GETDATE() SELECT @nowDateTime + 1 Result: 2011-09-17 13:44:31.247	FAILS – Need to use only DateAdd function Example: DECLARE @nowDateTime2 DATETIME2= SYSDATETIME() SELECT @nowDateTime2+1 Result: Msg 206, Level 16, State 2, Line 2 Operand type clash: datetime2 is incompatible with int