The SMALLDATETIME data type specifies a date and time of day in SQL Server.
SMALLDATETIME supports dates from 1900-01-01 through 2079-06-06.
The default value is 1900-01-01 00:00:00.
The seconds are always set to 0, and fractional seconds are not included.
This example creates a table with a SMALLDATETIME column.
CREATE TABLE DemoTable
(
Id INT IDENTITY,
ProductName VARCHAR(100) NOT NULL,
RestockDate SMALLDATETIME
);
GO
INSERT INTO DemoTable VALUES ('Lays Chips', '2021-02-15 9:55:12');
INSERT INTO DemoTable VALUES ('Quaker Oats', '2021/11/08 8AM');
INSERT INTO DemoTable VALUES ('Oreo Cookies', '2021-05-14 13:12');
INSERT INTO DemoTable VALUES ('Fritos', '2021-9-2');
INSERT INTO DemoTable VALUES ('Cheetos', NULL);
GO
SELECT * FROM DemoTable;
GO
DROP TABLE DemoTable;
GO
| Id | ProductName | RestockDate |
|---|---|---|
| 1 | Lays Potato Chips | 2021-02-15 09:55:00 |
| 2 | Quaker Oats | 2021-11-08 08:00:00 |
| 3 | Oreo Cookies | 2021-05-14 13:12:00 |
| 4 | Fritos | 2021-09-02 00:00:00 |
| 5 | Cheetos | NULL |
Notice how SQL Server accepts different input date and time formats.
These formats are converted to the internal format which is 'YYYY-MM-DD HH:MM:00'.
| ORDER |
|---|
| Id |
| OrderDate |
| OrderNumber |
| CustomerId |
| TotalAmount |
| CUSTOMER |
|---|
| Id |
| FirstName |
| LastName |
| City |
| Country |
| Phone |
SELECT OrderNumber, FirstName, LastName,
CAST(OrderDate AS SMALLDATETIME) AS [SmallDateTime],
TotalAmount
FROM [Order] O
JOIN Customer C ON C.Id = O.CustomerId
| OrderNumber | FirstName | LastName | SmallDateTime | TotalAmount |
|---|---|---|---|---|
| 542378 | Paul | Henriot | 2012-07-04 00:00:00 | 440.00 |
| 542379 | Karin | Josephs | 2012-07-05 00:00:00 | 1863.40 |
| 542380 | Mario | Pontes | 2012-07-08 00:00:00 | 1813.00 |
| 542381 | Mary | Saveley | 2012-07-08 00:00:00 | 670.80 |
| 542382 | Pascale | Cartrain | 2012-07-09 00:00:00 | 3730.00 |
 |
||||
CREATE TABLE DemoTable
(
MyDate DATE,
MyTime TIME,
MySmallDateTime SMALLDATETIME,
MyDateTime DATETIME,
MyDateTime2 DATETIME2,
MyDateTimeOffset DATETIMEOFFSET
);
GO
INSERT INTO DemoTable VALUES ('2021-10-22 13:54:19:55', '2021-10-22 13:54:19:55',
'2021-10-22 13:54:19:55', '2021-10-22 13:54:19:55',
'2021-10-22 13:54:19:55', '2021-10-22 13:54:19:55');
GO
SELECT * FROM DemoTable;
GO
DROP TABLE DemoTable;
GO
| MyDate | MyTime | MySmallDateTime | MyDateTime | MyDateTime2 | MyDateTimeOffset |
|---|---|---|---|---|---|
| 2021-10-22 | 13:54:19.0550000 | 2021-10-22 13:54:00 | 2021-10-22 13:54:19.057 | 2021-10-22 13:54:19.0550000 | 2021-10-22 13:54:19.0550000 +00:00 |
