Thursday, May 21, 2015

Difference between DateTime and DateTime2 DataType

DATETIME2 is the new data type introduced in SQL Server 2008

DATETIME2 provides more seconds precision & has larger date range, a larger default fractional precision. DATETIME is limited to 3 1/3 milliseconds, while DATETIME2 can be accurate down to 100ns

Advantages of DATETIME2
DATETIME2 has a date range of "0001 / 01 / 01" through "9999 / 12 / 31" while the DATETIME type only supports year 1753-9999
Larger range of values
Better Accuracy
Smaller storage space (if optional user-specified precision is specified)

Precision, scale
                                                                                                        0 to 7 digits, with an accuracy of 100nanoseconds.
                                                                                                        The default precision is 7 digits.
Storage Size
                                                                                                        6 bytes for precision less than 3
                                                                                                        7 bytes for precision 3 and 4.
All other precision require 8 bytes.
DATETIME2 with fractional seconds precision of 3 is same as DATETIME data type. And DATETIME2 (precision of 3) uses 7 bytes of storage instead of 8 byte

If you only need the date without time part use DATE
 If you only need the time without date part use TIME
 Using DATE & TIME data types instead of DATETIME2 in above situations you save the space