SQL Server SMALLDATETIME Data Type

SQL Server SMALLDATETIME stores a date and time from January 1, 1900, through June 6, 2079. It stores no seconds or fractional seconds: input values are rounded to the nearest minute. For new work, Microsoft recommends the DATE, TIME, DATETIME2, or DATETIMEOFFSET types instead; see the official SMALLDATETIME reference.

Syntax

The SMALLDATETIME data type is used with the following syntax:

SMALLDATETIME

SMALLDATETIME occupies 4 bytes. When SQL Server converts an input to this type, seconds of 29.998 or less round down to the current minute; seconds of 29.999 or more round up to the next minute. For example, 12:35:29 becomes 12:35:00, while 12:35:30 becomes 12:36:00.

Usage

Use SMALLDATETIME only when minute-level precision and its limited date range fit the application. It cannot store dates before 1900 or after June 6, 2079, so it is unsuitable for earlier historical dates as well as later dates.

The SMALLDATETIME data type can be used as a column in a data table. When creating a table, the name and data type of the SMALLDATETIME column can be specified. Date and time values can be inserted into the SMALLDATETIME column using an INSERT statement.

Examples

The following are two examples of using the SMALLDATETIME data type:

Example 1

Create a table named Orders with the OrderDate and ShipDate columns, both of which are SMALLDATETIME data types.

CREATE TABLE Orders (
   OrderID INT PRIMARY KEY,
   OrderDate SMALLDATETIME,
   ShipDate SMALLDATETIME
);

Insert some data:

INSERT INTO Orders (OrderID, OrderDate, ShipDate)
VALUES (1, '2022-03-10 13:30:00', '2022-03-12 10:00:00'),
       (2, '2022-03-11 14:00:00', '2022-03-13 12:00:00'),
       (3, '2022-03-12 15:00:00', '2022-03-14 11:30:00');

Query the Orders table:

SELECT * FROM Orders;

Output:

Example 2

Suppose there is a sales record table that contains two fields: sales date and sales amount. We can use the SMALLDATETIME data type to store the sales date.

CREATE TABLE Sales (
  SaleID INT PRIMARY KEY,
  SaleDate SMALLDATETIME,
  Amount MONEY
);

Now let’s insert some sales records into the Sales table.

INSERT INTO Sales VALUES
  (1, '2022-01-01 10:00', 100.00),
  (2, '2022-01-02 15:30', 50.00),
  (3, '2022-01-03 14:00', 200.00);

Use a half-open date range to find all sales recorded on January 2, 2022, regardless of the time of day:

SELECT * FROM Sales
WHERE SaleDate >= '2022-01-02'
  AND SaleDate < '2022-01-03';

The result is as follows:

Example 3

Suppose there is a meeting room booking table that includes fields for the start time and end time of a booking. We can use the SMALLDATETIME data type to store booking times.

CREATE TABLE MeetingRoomBookings (
  BookingID INT PRIMARY KEY,
  RoomNumber INT,
  StartTime SMALLDATETIME,
  EndTime SMALLDATETIME
);

Now let’s insert some booking records into the MeetingRoomBookings table.

INSERT INTO MeetingRoomBookings VALUES
  (1, 101, '2022-02-15 09:00', '2022-02-15 10:00'),
  (2, 102, '2022-02-15 10:00', '2022-02-15 12:00'),
  (3, 103, '2022-02-16 14:00', '2022-02-16 16:00');

Use an overlap condition to find bookings that intersect the 10:00 AM–12:00 PM window on February 15, 2022:

SELECT * FROM MeetingRoomBookings
WHERE StartTime < '2022-02-15 12:00'
  AND EndTime > '2022-02-15 10:00';

Result:

Conclusion

SMALLDATETIME rounds input to the nearest minute and supports dates only from 1900-01-01 through 2079-06-06. For new work, Microsoft recommends DATE, TIME, DATETIME2, or DATETIMEOFFSET; compare DATETIME and DATETIME2 when you need seconds or fractional-second precision.