Alvaro Dias

datetime vs. datetime2 which one you should use

Property datetime datetime2
Syntax datetime datetime2[(fractional seconds precision)]
Date Range 1753-01-01 through 9999-12-31 0001-01-01 through 9999-12-31
Time Range 00:00:00 through 23:59:59.997 00:00:00 through 23:59:59.9999999
Accuracy rounded to increments of .000, .003, or .007 seconds 100 nanoseconds
Precision 3 sub-second digits 0 to 7 sub-second digits, with an accuracy of 100ns. The default precision is 7 digits
Storage Size 8 bytes 6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes
Compliance Not ANSI or ISO 8601 compliant The ANSI and ISO 8601 compliance of date and time apply to datetime2
Other Comments This is mainly available for backward compatible scenarios I would recommend everyone to use this datatype unless it needs to be backward compatible with some legacy code. Be careful when passing a DateTime parameter from .NET to pass a value which is outside the range of datetime, you would need to explicitly set the SqlDbType to SqlDbType.DateTime2
Common Function Equivalents GETDATE & CURRENT_TIMESTAMP
GETUTCDATE
SYSDATETIME
SYSUTCDATETIME
SYSDATETIMEOFFSET