The DATETIME2
data type specifies a date and time with fractional seconds.
DATETIME2
supports dates from 0001-01-01 through 9999-12-31.
The default value is 1900-01-01 00:00:00. The time is based on a 24-hour clock.
DATETIME2
is an extension of the DATETIME data type
with a larger date range and with fractional precision. Precision is optionally defined by the user.
The example creates a table with a DATETIME2
column.
CREATE TABLE DemoTable
(
Id INT IDENTITY,
Customer VARCHAR(100) NOT NULL,
ProductName VARCHAR(100) NOT NULL,
ShippingDateTime DATETIME2
);
GO
INSERT INTO DemoTable VALUES ('Harold Smith', 'Lays Chips', '2022-02-15 9:55:12');
INSERT INTO DemoTable VALUES ('Robert Wang', 'Quaker Oats', '2022/11/08 8AM');
INSERT INTO DemoTable VALUES ('Janice Lopez', 'Oreo Cookies', '2022-05-14 13:12');
INSERT INTO DemoTable VALUES ('Kelly Wilson', 'Fritos', '2022-9-2');
INSERT INTO DemoTable VALUES ('Grace Taylor', 'Cheetos', NULL);
GO
SELECT * FROM DemoTable;
GO
DROP TABLE DemoTable;
GO
Id | Customer | ProductName | ShippingDateTime |
---|---|---|---|
1 | Harold Smith | Lays Potato Chips | 2022-02-15 09:55:12.0000000 |
2 | Robert Wang | Quaker Oats | 2021-11-08 08:00:00.0000000 |
3 | Janice Lopez | Oreo Cookies | 2021-05-14 13:12:00.0000000 |
4 | Kelly Wilson | Fritos | 2021-09-02 00:00:00.0000000 |
5 | Grace Taylor | 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:SS.NNNNNNN'.
Syntax of DATETIME2.
DATETIME2(precision)
precision
-- optional, fractional seconds precision. It accepts values from 0 to 7. The default is 7.
ORDER |
---|
Id |
OrderDate |
OrderNumber |
CustomerId |
TotalAmount |
CUSTOMER |
---|
Id |
FirstName |
LastName |
City |
Country |
Phone |
SELECT OrderNumber, FirstName,
LastName, OrderDate,
TotalAmount
FROM [Order] O
JOIN Customer C ON C.Id = O.CustomerId
Note: OrderDate is a DATETIME2
column in the Order table.
OrderNumber | FirstName | LastName | OrderDate | TotalAmount |
---|---|---|---|---|
542378 | Paul | Henriot | 2012-07-04 00:00:00.000 | 440.00 |
542379 | Karin | Josephs | 2012-07-05 00:00:00.000 | 1863.40 |
542380 | Mario | Pontes | 2012-07-08 00:00:00.000 | 1813.00 |
542381 | Mary | Saveley | 2012-07-08 00:00:00.000 | 670.80 |
542382 | Pascale | Cartrain | 2012-07-09 00:00:00.000 | 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 |