DateTime2 vs DateTime in SQL Server

In SQL Server, managing date and time values for various applications ranging from transaction processing to reporting. SQL Server offers two main data types for handling date and time:

• DateTime
• DateTime2

While both serve similar purposes, they differ significantly in terms of precision, storage requirements, and range of supported values. In this article, we’ll understand the key differences between both of the data types and how they impact the way we store data in the database.

DateTime Data Type

The DateTime data type in SQL Server is used to store date and time values with a precision of approximately 3.33 milliseconds. It covers a range from January 1, 1753, to December 31, 9999, with accuracy to the nearest three milliseconds.

Syntax

CREATE TABLE ExampleDateTime (
    ID INT PRIMARY KEY,
    EventDateTime DateTime
);

Example of DateTime datatype

Suppose we have a table named EventLogDateTime with columns LogID and LogDateTime. We want to store date and time values in the column LogDateTime .

-- Creating a table with DateTime data type
CREATE TABLE EventLogDateTime (
    LogID INT PRIMARY KEY,
    LogDateTime DateTime
);

-- Inserting data into the DateTime table
INSERT INTO EventLogDateTime (LogID, LogDateTime)
VALUES 
    (1, '2023-07-15 14:30:00'),
    (2, '2023-07-16 10:45:00');

-- Querying data from the DateTime table
SELECT * FROM EventLogDateTime;

Output:

+-------+---------------------+
| LogID | LogDateTime         |
+-------+---------------------+
|     1 | 2023-07-15 14:30:00 |
|     2 | 2023-07-16 10:45:00 |
+-------+---------------------+

Explanation: The output displays all date time data we are storing LogDateTime column.

DateTime2 Data Type

The DateTime2 data type is an extension introduced in SQL Server 2008 to address limitations in DateTime. It provides higher precision for storing date and time values which is supporting up to 7 decimal places for fractional seconds.

Syntax:

CREATE TABLE ExampleDateTime2 (
    ID INT PRIMARY KEY,
    EventDateTime2 DateTime2(3) -- specifying precision (optional)
);

Example of DateTime2 datatype

Suppose we have a table named EventLogDateTime2 with columns LogID and LogDateTime2. We want to store date and time values in column LogDateTime2.

-- Creating a table with DateTime2 data type
CREATE TABLE EventLogDateTime2 (
    LogID INT PRIMARY KEY,
    LogDateTime2 DateTime2(3)
);

-- Inserting data into the DateTime2 table
INSERT INTO EventLogDateTime2 (LogID, LogDateTime2)
VALUES 
    (1, '2023-07-15 14:30:00.123'),
    (2, '2023-07-16 10:45:00.456');

-- Querying data from the DateTime2 table
SELECT * FROM EventLogDateTime2;

Output:

---------------------------------
| LogID |       LogDateTime2     |
---------------------------------
|   1   | 2023-07-15 14:30:00.123 |
|   2   | 2023-07-16 10:45:00.456 |
---------------------------------

Explanation: The output displays all date time data we are storing LogDateTime column.

Differences Between DateTime and DateTime2

Conclusion

Choosing between DateTime and DateTime2 depends on the specific requirements of the application. We can use DateTime for general-purpose datetime storage where milliseconds precision is sufficient. But DateTime2 can be used when we need higher precision, up to nanoseconds, for accurate timestamp tracking or time-sensitive applications. Understanding these differences ensures optimal use of date and time data types in SQL Server databases, aligning with performance and data accuracy goals.