Sunday, 28 August 2016

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.
DateTimeDateTime2[(n)]
Min Value1753-01-01 00:00:000001-01-01 00:00:00
Max Value9999-12-31 23:59:59.9979999-12-31 23:59:59.9999999
Storage Size8 Bytes6 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
UsageDeclare @now datetimeDeclare @now datetime2(7)
ComplianceIs not an ANSI/ISO compliantIs an ANSI/ISO compliant
Current Date and Time functionGetDate() – 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
+/- daysWORKS
Example: 
DECLARE
 @nowDateTimeDATETIME = 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
ResultMsg 206, Level 16, State 2, Line 2
Operand type clash: datetime2 is incompatible with int

No comments:

Post a Comment