SQL Date and Time Types Compared

DATE, DATETIME, DATETIME2, and DATETIMEOFFSET

The main differences are whether they store time, fractional seconds, and a time-zone offset.

1. DATE

Stores only the calendar date—no time.

DECLARE @BirthDate DATE = '1980-05-20';

Use it for:

• Date of birth

• Joining date

• Invoice date

• Holiday date

2. DATETIME

Stores both date and time, but it is an older data type with limited range and precision.

DECLARE @CreatedAt DATETIME = '2026-09-17 10:30:25.123';

Its time is rounded to increments ending approximately in:

.000, .003 or .007 seconds

Use it mainly when working with legacy databases. For new development, prefer DATETIME2.

3. DATETIME2

Stores date and time with better range, configurable precision and accuracy.

DECLARE @CreatedAt DATETIME2(3) = '2026-09-17 10:30:25.123';

The number specifies the fractional-second precision:

DATETIME2(0) -- No fractional seconds
DATETIME2(3) -- Milliseconds
DATETIME2(7) -- 100-nanosecond precision

Use it for:

• Created and modified timestamps

• Audit records

• Transaction timestamps

• General date-and-time storage

For most applications, DATETIME2(3) is sufficient when millisecond precision is needed.

4. DATETIMEOFFSET

Stores the date, time and its offset from UTC.

DECLARE @MeetingTime DATETIMEOFFSET(3)
    = '2026-09-17 10:30:25.123 +05:30';

Here, +05:30 means the value is five hours and thirty minutes ahead of UTC.

Use it for:

• Global applications

• Events occurring in different time zones

• Recording the original UTC offset

• Cross-region auditing

Two values can represent the same UTC instant:

2026-09-17 10:30:00 +05:30
2026-09-17 05:00:00 +00:00

Recommended selection

CREATE TABLE Employee
(
    BirthDate  DATE,
    CreatedAt  DATETIME2(3),
    MeetingAt  DATETIMEOFFSET(3)
);

• Use DATE when time is unnecessary.

• Use DATETIME2 for general date and time.

• Use DATETIMEOFFSET when the offset matters.

• Use DATETIME mainly for legacy compatibility.

Important timezone limitation

DATETIMEOFFSET stores a numeric offset such as +05:30; it does not store a time-zone name such as India Standard Time.

Offsets alone cannot represent daylight-saving rules. If the actual time zone is important, store its name separately.

Key interview points

• DATE stores only a date.

• DATETIME is a legacy type with lower precision and a smaller date range.

• DATETIME2 is the preferred date-time type for new applications.

• DATETIMEOFFSET stores date, time and UTC offset.

• DATETIME2 and DATETIMEOFFSET support up to seven fractional-second digits.

• Store global system timestamps in UTC where appropriate.

• Use ISO 8601 formats to avoid language-dependent conversion issues:

'2026-09-17T10:30:25.123'