datetime2

DATETIME2 is a date and time data type used in SQL Server and Azure SQL Database. It stores both date and time information with configurable fractional-second precision, from 0 to 7. The default precision is 7, allowing up to 100 nanosecond resolution.

The data type supports a broad date range: 0001-01-01 through 9999-12-31, with the time portion ranging from 00:00:00 to 23:59:59.9999999. Storage for DATETIME2 varies with the chosen precision, typically between 6 and 8 bytes.

DATETIME2 is designed as a higher-precision, wider-range successor to the older DATETIME type (and SMALDATETIME). It does not store time zone information; for time zone awareness, DATETIMEOFFSET should be used. In many new designs, DATETIME2 is recommended over DATETIME due to its greater range and precision.

The data type is declared as DATETIME2[(n)], where n is the fractional-second precision from 0 to 7. For example, DATETIME2(3) stores milliseconds precision, while DATETIME2 without an explicit precision uses the default of 7. When converting from other types or string literals, the resulting precision may affect rounding.

Related functions and usage include casting and converting values to and from DATETIME2, and using system functions such as SYSDATETIME() (which returns a DATETIME2 value) and GETDATE() (which returns a DATETIME value). CURRENT_TIMESTAMP is equivalent to GETDATE().

In practice, DATETIME2 is commonly used for accurate timestamps, event logging, and any scenario requiring precise sub-second timing, while DATE and DATETIMEOFFSET offer alternative representations for specific needs.

---