| 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 |
RSS
