SQL Server Interview Question #27

Which date and time data types are commonly used in SQL Server?

Data Types, Keys & Constraints Junior Beginner

Detailed Explanation

Common SQL Server date/time types include DATE, TIME, DATETIME, DATETIME2, SMALLDATETIME, and DATETIMEOFFSET.

DATE stores only a date. TIME stores only a time. DATETIME2 stores date and time with a wider range and greater fractional-second precision than the older DATETIME type. DATETIMEOFFSET additionally stores a UTC offset.

For new application development, DATETIME2 is generally preferable to DATETIME when an offset is not required. For systems where the offset itself matters, DATETIMEOFFSET can be appropriate.

Code Example

CREATE TABLE Orders
(
    Id INT IDENTITY PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CreatedAt DATETIME2 NOT NULL DEFAULT SYSDATETIME()
);