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

No comments:

Post a Comment

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...