SQL Server SMALLDATETIME Data Type

SQL Server is a relational database management system that supports various data types, including the SMALLDATETIME data type. SMALLDATETIME is a date and time data type that is used to store a combination of date and time.

Syntax

The SMALLDATETIME data type is used with the following syntax:

SMALLDATETIME

The SMALLDATETIME data type occupies 4 bytes of storage space and can represent dates and times between January 1, 1900, and June 6, 2079. Its precision is at the minute level, accurate to the first or second minute.

Usage

The SMALLDATETIME data type is commonly used in business applications that require the storage of date and time information. It is suitable for storing earlier date and time information because it does not support earlier 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);

We can use the following query statement to find the sales records on January 2, 2022.

SELECT * FROM Sales
WHERE SaleDate = '2022-01-02';

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');

We can use the following query to find the booking records between 10:00 AM and 12:00 PM on February 15th, 2022.

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

Result:

Conclusion

The SMALLDATETIME data type is suitable for storing dates and times when high precision is not required. It can only store up to minute-level accuracy, so DATETIME or DATETIME2 data types should be used when storing second-level or higher precision is required.