SQL Server uses binary formats to store all date and time data types. These formats vary depending on the specific data type chosen, and SQL Server internally represents each date type using a specific structure of integers, fractions, or offsets. Let’s explore each major date type in SQL Server and how it is stored internally.

DATETIME (8 bytes)

The DATETIME data type is one of the most commonly used for storing both date and time information. It is stored using 8 bytes of memory.

Structure:

• First 4 bytes: Represent the number of days since the base date, which is January 1, 1900.

• Second 4 bytes: Represent the number of 1/300-second increments that have passed since midnight on the stored date.

Example:

Let’s break down a specific example to understand this storage method:

• For the date '1900-01-02 00:00:00.000', SQL Server stores the following:

  • Days since January 1, 1900: 1 (the next day).

  • Time part (1/300-second increments): 0 (since it’s midnight).

Thus, the DATETIME value of '1900-01-02 00:00:00.000' is stored as:

• 1 for the day offset.

• 0 for the time part.

If we use the value '1900-01-01 12:00:00.000', this represents noon on January 1st, 1900:

• Days offset: 0.

• Time part: 108000 (calculated as 300 * 60 * 60 * 12, which represents 12 hours in 1/300-second increments).

This storage mechanism makes DATETIME extremely efficient for storing a large range of dates and times.

SQL Server historically interprets 1900-01-01 as day zero. However, it incorrectly treats 1900 as a leap year — a result of compatibility with older systems, such as Microsoft Excel, which used this as a base date.

SMALLDATETIME (4 bytes)

The SMALLDATETIME data type is similar to DATETIME but offers less precision and a smaller range. It uses 4 bytes of storage.

Structure:

• First 2 bytes: Represent the number of days since 1900-01-01.

• Second 2 bytes: Represent the number of minutes that have passed since midnight.

The SMALLDATETIME type supports a smaller date range from 1900-01-01 to 2079-06-06 and only stores time up to the minute level of precision.

Example:

For the value '1900-01-02 12:30:00.000', the internal representation is:

• Days offset: 1.

• Minutes offset: 750 (which is 12 * 60 + 30 for 12:30 PM).

DATE (3 bytes)

Introduced in SQL Server 2008, the DATE data type stores only the date (without the time component). It uses 3 bytes of memory for storage.

Structure:

• Single integer representing the number of days since 0001-01-01 (the beginning of the Gregorian calendar). This allows for dates between 0001-01-01 and 9999-12-31.

Example:

For the value '2025-04-08', the internal representation is:

• Days offset: 736946 (the number of days from 0001-01-01 to 2025-04-08).

This storage method makes DATE a highly efficient type for storing date-only values without the overhead of time storage.

TIME(n) (3 to 5 bytes)

The TIME data type is used to store only the time portion of a DATETIME. The TIME(n) type can store times with fractional seconds, where n indicates the precision level.

Structure:

• The number of bytes used for storage depends on the fractional precision n, and it ranges from 3 to 5 bytes.

• The time value is stored as the number of 100-nanosecond units since midnight (00:00:00).

For example:

• TIME(0) uses 3 bytes to store the time to seconds precision.

• TIME(7) uses 5 bytes, storing the time to 100-nanosecond precision.

Example:

For the value '12:30:45.1234567':

• The internal representation would be an integer equivalent to the number of 100-nanosecond intervals since midnight.

DATETIME2(n) (6 to 8 bytes)

DATETIME2 is a more precise and storage-efficient alternative to DATETIME. The precision level is determined by n, which defines the number of fractional seconds to store.

Structure:

• Days since 0001-01-01 (similar to DATE).

• Fractional seconds since midnight stored as 100-nanosecond ticks.

The number of bytes used for DATETIME2 depends on the precision n:

• 6 bytes for n ≤ 3.

• 7 bytes for n ≤ 4.

• 8 bytes for n ≤ 7.

This makes DATETIME2 ideal for scenarios requiring more precise time storage, as it can store up to 7 decimal places for fractional seconds.

DATETIMEOFFSET(n) (8 to 10 bytes)

The DATETIMEOFFSET type combines the features of DATETIME2 with an offset for time zones. This allows you to store the date and time along with the time zone offset, making it perfect for applications that need to handle global times.

Structure:

• Days since 0001-01-01.

• Fractional seconds stored as 100-nanosecond ticks (like DATETIME2).

• Two additional bytes store the time zone offset in minutes.

The number of bytes used depends on the precision n:

• 8 bytes for n ≤ 3 (standard precision).

• 10 bytes for n ≤ 7 (higher precision).

Why SQL Server Stores Dates as Integers

SQL Server’s decision to store date and time values as binary integers or floating-point numbers provides several performance and efficiency benefits:

Efficient Date Arithmetic

Date and time arithmetic (such as adding days, hours, or minutes) is performed efficiently using simple integer or fractional operations. This is far faster than manipulating date strings, which require more complex parsing and formatting.

For example, adding one day to a DATETIME value is simply an integer addition, making it a very fast operation. In contrast, adding days to a date string would involve string parsing, which is computationally more expensive.

Efficient Indexing

Date values stored as integers allow for highly efficient indexing and sorting. SQL Server uses B-tree indexes, where sorting and comparisons are simplified because dates are just integers under the hood. This makes queries that filter by date extremely fast, as the database can compare integer values directly.

Compact Storage

By using a binary format rather than a string, SQL Server can store dates much more compactly. For example, the DATE type uses only 3 bytes, making it ideal for scenarios where you only need the date component. The compact storage also reduces the I/O load when retrieving large datasets with date fields.

Compatibility

The integer-based storage system ensures compatibility with legacy systems and formats. For example, earlier versions of Microsoft Excel also used similar representations for date-time values, making it easier to exchange data across platforms.

Viewing Internal Representations

While SQL Server hides the internal representations of dates, you can still inspect how SQL Server stores these values using CAST() and VARBINARY to see the raw binary data.

Example: