DATETIME vs DATETIME2

The new data type DATETIME2 is introduced in Microsoft SQL Server 2008.

It is recommended by Microsoft to use DATETIME2 instead of previous version DATETIME.

Before using the DATETIME2 we have to understand the differences between DATETIME and DATETIME2.

The differences are mentioned in the tabular format

Options	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 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
How to use it	DECLARE @param DATETIME	DECLARE @param DATETIME2(7)
Compliance	Is not an ANSI/ISO compliant	Is an ANSI/ISO compliant
Function Used	GETDATE() – It returns DB Current Date and Time of DateTime Data Type	SYSDATETIME()- It returns DB Current Date and Time of DateTime2 Data Type Example: SELECT SYSDATETIME()
+/- of (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

Hope you like it.

Posted by: MR. JOYDEEP DAS