This article will teach you what is the difference between
DATETIME2 in SQL Server. Both represent date types of date. They are used to store date and time information. They are used when declaring the type of a variable or in the definition of a column of a table from the database.
In SQL Server, the
DATETIME data type is used to store date and time information.
It has a precision of 3.33 milliseconds and a range of January 1, 1753 to December 31, 9999.
DATETIME2 data type is an enhanced version of DATETIME with a higher precision of 100 nanoseconds and a larger date range of 0001-01-01 to 9999-12-31.
Additionally, DATETIME2 supports time zone offset information, whereas DATETIME does not.
Here are some examples of using the DATETIME and DATETIME2 data types in SQL Server:
-- Using the DATETIME data type
DECLARE @date1 DATETIME = '2022-01-01 12:30:00'
-- Using the DATETIME2 data type
DECLARE @date2 DATETIME2 = '2022-01-01 12:30:00.1234567'
In this example, we declare two variables, @date1 and @date2, using the DATETIME and DATETIME2 data types, respectively. We then assign a date and time value to each variable and use the SELECT statement to display their values.
-- Creating a table with a DATETIME column
CREATE TABLE Orders (
-- Inserting a new row into the Orders table
INSERT INTO Orders (OrderID, OrderDate, OrderTotal)
VALUES (1, '2022-01-01 12:30:00', 100.00)
-- Creating a table with a DATETIME2 column
CREATE TABLE Orders2 (
-- Inserting a new row into the Orders2 table
INSERT INTO Orders2 (OrderID, OrderDate, OrderTotal)
VALUES (1, '2022-01-01 12:30:00.1234567', 100.00)
In this example, we create two tables named Orders and Orders2. Orders table has a column named OrderDate of DATETIME data type and Orders2 table has a column named OrderDate of DATETIME2 data type with precision of 7. Then we insert a row into each table with the date and time value that corresponds to the precision of the data type.