This article will teach you what is the difference between DATETIME and 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.

The 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.

Example

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'
SELECT @date1

-- Using the DATETIME2 data type
DECLARE @date2 DATETIME2 = '2022-01-01 12:30:00.1234567'
SELECT @date2

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.

Another example

-- Creating a table with a DATETIME column
CREATE TABLE Orders (
OrderID INT,
OrderDate DATETIME,
OrderTotal DECIMAL(10,2)
);

-- 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 (
OrderID INT,
OrderDate DATETIME2(7),
OrderTotal DECIMAL(10,2)
);

-- 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.