SQL Date/Time Storage and Precision Guide

datetime

Stores date and time; range from 1753 to 9999; about 3ms precision; uses 8 bytes; suitable for legacy systems.

datetime2

Improved version of datetime; range from 0001 to 9999; supports up to 7-digit fractional seconds; uses 6–8 bytes; preferred for new applications.

datetimeoffset

Like datetime2 but includes time zone offset; ideal for global or multi-time zone data; uses 8–10 bytes.

date

Stores only the date (no time); range from 0001 to 9999; uses 3 bytes; useful for birthdates, holidays, and any date-only fields.

time

Stores only the time (no date); supports precision up to 7 fractional seconds; uses 3–5 bytes; ideal for durations or business hours.