DATETIME2 vs DATETIME in SQL Server

1. Overview

In SQL Server, we use the DATETIME and DATETIME2 data types for multiple purposes. For example, we can use these data types for storing date and time, variable declarations, or defining table columns.

DATETIME and DATETIME2 perform similar tasks but have small differences in accuracy, precision, time range, storage capacity, and date range.

In this tutorial, we’ll explain what the DATETIME and DATETIME2 data types are and how they differ.

2. DATETIME

DATETIME is an older and well-known data type option available in SQL Server for storing date and time. Furthermore, we can use DATETIME to store dates from January 1, 1753, to December 31, 9999, using eight bytes of storage.

While DATETIME is still supported, we often recommend using the newer DATETIME2 data type, as it offers better precision for date and time values.

2.1. Example Query

Let’s first create a table named Appointments with a column called AppointmentDate of type DATETIME:

CREATE TABLE Appointments (
AppointmentDate DATETIME
);

Next, we need to insert a date and time value:

INSERT INTO Appointments (AppointmentDate)
VALUES ('2024-08-15 10:30:00.000');

In this query, we insert a value into the AppointmentDate column using the INSERT INTO statement. In addition, we specified the date and time value in the format YYYY-MM-DD HH:MM:SS.mmm, representing August 15, 2024, at 10:30:00 AM with no milliseconds.

Additionally, we retrieve rows based on a specific date and time using the WHERE clause:

SELECT AppointmentDate
FROM Appointments
WHERE AppointmentDate = '2024-08-15 10:30:00.000';
+---+-------------------------+
|   | AppointmentDate         |
|---+-------------------------|
| 1 | 2024-08-15 10:30:00.000 |
+---+-------------------------+

Furthermore, we can also use a DATETIME variable to store the date and time value:

DECLARE @date1 DATETIME = '2024-08-15 10:30:00';

CREATE TABLE Appointments (
AppointmentDate DATETIME
);

INSERT INTO Appointments (AppointmentDate)
VALUES (@date1);

SELECT AppointmentDate
FROM Appointments
WHERE AppointmentDate = @date1;
+---+-------------------------+
|   | AppointmentDate         |
|---+-------------------------|
| 1 | 2024-08-15 10:30:00.000 |
+---+-------------------------+

After declaring the variable at the start, we can use that variable in the INSERT statement to specify the DATETIME value.

3. DATETIME2

Starting with SQL Server 2008, Microsoft introduced a new data type called DATETIME2 that offers more features than the older DATETIME.

With the DATETIME2 data type, we can cover a larger date range, which includes dates from January 1, 0001, to December 31, 9999. Additionally, we can store date and time values using variable storage sizes ranging from six to eight bytes, depending on the precision needed.

In addition, we can align DATETIME2 with SQL standards like ANSI and ISO 8601 to make our code more compatible.

3.1. Example Query

Let’s create a table named Events with a column called EventTime of type DATETIME2 that has the default precision of seven decimal places for fractional seconds:

CREATE TABLE Events (
EventTime DATETIME2
);

Next, we insert the event date and time using the INSERT INTO:

INSERT INTO Events (EventTime)
VALUES ('2024-08-05 10:30:00.12345');

Finally, we select the EventTime column from the Events table using WHERE:

SELECT EventTime
FROM Events
WHERE EventTime = '2024-08-05 10:30:00.12345';
+---+-----------------------------+
|   | EventTime                   |
|---+-----------------------------|
| 1 | 2024-08-05 10:30:00.1234500 |
+---+-----------------------------+

Moreover, we can also declare the DATETIME2 variable using the DECLARE keyword:

DECLARE @date2 DATETIME2 = '2022-01-01 12:30:00.1234567';
SELECT @date2;
+---+-----------------------------+
|   |                             |
|---+-----------------------------|
| 1 | 2022-01-01 12:30:00.1234567 |
+---+-----------------------------+

After the declaration, we use the SELECT statement to display the DATETIME2 variable value.

4. DATETIME2 vs DATETIME

DATETIME and DATETIME2 have many similarities and can perform almost the same functions in SQL Server. However, the newer DATETIME2 is an improved version of DATETIME and has a slightly different format, syntax, accuracy, and date and time range.

4.1. Comparison

Let’s look at some of the differences between DATETIME and DATETIME2:

Furthermore, we should go with DATETIME when compatibility is more important than precision.

4.2. Precision Difference

Let’s demonstrate the precision difference between DATETIME and DATETIME2:

DECLARE @datetime2 DATETIME2(7), @datetime DATETIME;
SET @datetime2 = '2024-08-06 18:34:10.2333333';
SET @datetime = '2024-08-06 18:34:10.233';
SELECT @datetime AS 'datetime', @datetime2 AS 'datetime2';
+---+-------------------------+-----------------------------+
|   | datetime                | datetime2                   |
|---+-------------------------|-----------------------------|
| 1 | 2024-08-06 18:34:10.233 | 2024-08-06 18:34:10.2333333 |
+---+-------------------------+-----------------------------+

In this query, DATETIME2 shows a fractional seconds precision of seven digits, while DATETIME has a precision of three digits.

In addition, we need to ensure that the string we’re assigning to the DATETIME variable only contains three fractional seconds.

4.3. Adding Day

One drawback of DATETIME2 is that basic math operations, such as adding a number to a date, don’t work as expected.

For example, let’s add a day to DATETIME:

DECLARE @date1 DATETIME;
SET @date1 = GETDATE();
SELECT @date1 + 1 as Tomorrow;
+---+-------------------------+
|   | Tomorrow                |
|---+-------------------------|
| 1 | 2024-08-07 20:08:36.730 |
+---+-------------------------+

Now, let’s add a day to DATETIME2:

DECLARE @date2 DATETIME2(7);
SET @date2 = GETDATE();
SELECT @date2 + 1 as Tomorrow;
Operand type clash: datetime2 is incompatible with int

Here, the above query returns an error because SQL Server doesn’t support adding integers directly to DATETIME2 values.

4.4. Rounding Fractional Seconds

When we assign a larger value to a DATETIME, it will round the fractional seconds to fit the precision. Let’s see an example:

DECLARE @date1 datetime, @date2 datetime2(3);
SET @date2 = '2024-12-03 11:25:20.5782441';
SET @date1 = @date2;
SELECT @date1 AS 'datetime', @date2 AS 'datetime2';
+---+-------------------------+-----------------------------+
|   | datetime                | datetime2                   |
|---+-------------------------|-----------------------------|
| 1 | 2024-12-03 11:25:20.577 | 2024-12-03 11:25:20.578     |
+---+-------------------------+-----------------------------+

Here, the DATETIME truncates the 5782441 to 577 due to its precision of 3.33 milliseconds (0.003 seconds). This means it doesn’t store fractional seconds with full precision, but rather rounds to the nearest 0.003 second increment.

Moreover, we can retain the 578 value with the DATETIME2(3) type, as it has a precision of 3 decimal places for fractional seconds.

5. Conclusion

In this article, we explored how DATETIME2 is different from the DATETIME data type in SQL Server using various examples.

Furthermore, we prefer DATETIME2 over DATETIME due to its larger date range, higher precision, and better storage efficiency. However, Datetime2 doesn’t support simple math queries with dates, while DATETIME supports them.