The SMALLDATETIME data type in SQL Server is used to store date and time values but with a limited range and precision. It provides a compact way to store date and time information when high precision isn’t required, and it occupies less storage than other datetime types, such as DATETIME.

Key Features

Storage Size:

SMALLDATETIME requires 4 bytes of storage, which is smaller than the 8 bytes required by the DATETIME type. This makes it a more storage-efficient option when precise time information down to fractions of a second is unnecessary.

Range:

It can store date and time values from January 1, 1900 to June 6, 2079. This limited range is something to consider, especially if dealing with dates outside of this period.

Precision:

The SMALLDATETIME type has a 1-minute precision, meaning it does not store seconds. Any time value inserted with seconds will be rounded to the nearest minute. For instance, 12:30:45 would be stored as 12:31.
Format:

Dates in SMALLDATETIME are typically stored in the format YYYY-MM-DD hh:mm:ss, though seconds will always be :00 due to the rounding.

Default Values:

When a SMALLDATETIME field is declared without an explicit value, it defaults to 1900-01-01 00:00:00.

Example

Here’s how you might use SMALLDATETIME in a table definition:

CREATE TABLE EventLog (
    EventID INT PRIMARY KEY,
    EventDate SMALLDATETIME,
    Description NVARCHAR(255)
);

Inserting values into this table would look like this:

INSERT INTO EventLog (EventID, EventDate, Description)
VALUES (1, '2023-11-12 14:45', 'System check complete');

In this example, if you try to insert 2023-11-12 14:45:30, SQL Server will round it to 2023-11-12 14:45.

When to Use SMALLDATETIME

SMALLDATETIME is ideal when:

You need to save space and do not need the full precision of DATETIME.
The dates fall within the supported range (1900 to 2079).
You only need minute-level precision.
However, if more precision or a wider range of dates is required, consider using DATETIME, DATETIME2, or DATE types instead.