There are probably very few use cases that would cause you to convert a datetime2 value to datetime in SQL Server. In particular, the datetime2 data type can be set to use the same storage size as datetime, but with higher precision. So in most cases you’d be better off with datetime2 than with a datetime. Microsoft also recommends using datetime2 instead of datetime.

However, in case you find yourself in the situation where you need to perform this conversion, this article contains some examples and considerations that may be of assistance.

When you convert a datetime2 value to datetime, the resulting value will depend on the fractional seconds that were assigned to the datetime2 value, as well as its precision.

The datetime2 data type allows you to specify a fractional seconds precision from 0 to 7. If you don’t specify this, it will use 7 (the default).

The datetime data type on the other hand, has a maximum of 3 digits for its fractional seconds part. Its accuracy is rounded to increments of .000, .003, or .007 seconds.

Therefore, if the datetime2 uses a scale of 3, the resulting value will be very close (if not identical) to the original value. However, due to the lower accuracy of datetime, the results can be different, because of the rounding that it performs.

Example 1 – Implicit Conversion

Here’s an example of an implicit conversion between datetime2 and datetime.

DECLARE 
  @thedatetime2 datetime2, 
  @thedatetime datetime;
SET @thedatetime2 = '2025-05-21 10:15:30.1234567';
SET @thedatetime = @thedatetime2;
SELECT 
  @thedatetime2 AS 'datetime2',
  @thedatetime AS 'datetime';

Result:

+-----------------------------+-------------------------+
| datetime2                   | datetime                |
|-----------------------------+-------------------------|
| 2025-05-21 10:15:30.1234567 | 2025-05-21 10:15:30.123 |
+-----------------------------+-------------------------+

This is an implicit conversion because we’re not using a conversion function (like the ones below) to explicitly convert it. In this case, SQL Server performs an implicit conversion behind the scenes when we try to assign the datetime2 value to a datetime variable.

We can see that the datetime variable has less fractional seconds precision, and we end up with a fractional part of 123 even though the original fractional part was 1234567.

In this case, no rounding was performed.

Example 2 – Precision/Accuracy and Rounding

The datetime data type is rounded to increments of .000, .003, or .007 seconds. Even if you explicitly set it to another value, it will be rounded.

This also applies when converting from another data type (such as what we’re doing here).

Here’s an example that demonstrates what I mean.

DECLARE 
  @thedatetime2 datetime2, 
  @thedatetime datetime;
SET @thedatetime2 = '2025-05-21 10:15:30.1256789';
SET @thedatetime = @thedatetime2;
SELECT 
  @thedatetime2 AS 'datetime2',
  @thedatetime AS 'datetime';

Result:

+-----------------------------+-------------------------+
| datetime2                   | datetime                |
|-----------------------------+-------------------------|
| 2025-05-21 10:15:30.1256789 | 2025-05-21 10:15:30.127 |
+-----------------------------+-------------------------+

In this example, I set the fractional seconds of the datetime2 value to 1256789 but datetime rounded it up to 127 (because it can only be rounded to increments of .000, .003, or .007 seconds).

It’s important to note that this will still be true, even if we assign only 3 fractional seconds to the datetime2 value.

Example:

DECLARE 
  @thedatetime2 datetime2(3), 
  @thedatetime datetime;
SET @thedatetime2 = '2025-05-21 10:15:30.125';
SET @thedatetime = @thedatetime2;
SELECT 
  @thedatetime2 AS 'datetime2',
  @thedatetime AS 'datetime';

Result:

+-------------------------+-------------------------+
| datetime2               | datetime                |
|-------------------------+-------------------------|
| 2025-05-21 10:15:30.125 | 2025-05-21 10:15:30.127 |
+-------------------------+-------------------------+

It also pays to be aware of any rounding that might occur to the original datetime2 value. The datetime2 value could itself be rounded up if we try to assign a value with more fractional seconds than its own scale.

Example:

DECLARE 
  @thedatetime2 datetime2(3), 
  @thedatetime datetime;
SET @thedatetime2 = '2025-05-21 10:15:30.1256789';
SET @thedatetime = @thedatetime2;
SELECT 
  @thedatetime2 AS 'datetime2',
  @thedatetime AS 'datetime';

Result:

+-------------------------+-------------------------+
| datetime2               | datetime                |
|-------------------------+-------------------------|
| 2025-05-21 10:15:30.126 | 2025-05-21 10:15:30.127 |
+-------------------------+-------------------------+

In this case, I try to assign a value with a fractional part of 1256789. However, because the datetime2(3) value only has a scale of 3, it can only support 3 decimal places, and in this case, the last digit is rounded up (because the following digit is 5 or higher).

Both datetime2(3) and datetime use the same amount of storage space (8 bytes). The datetime2(3) data type actually uses 7 bytes to store the data, but extra 1 byte to store the precision.

Example 3 – Explicit Conversion using CAST()

Here’s an example of an explicit conversion. In this case, I use the CAST() function directly within the SELECT statement to explicitly convert between datetime2 and datetime.

DECLARE @thedatetime2 datetime2;
SET @thedatetime2 = '2025-05-21 10:15:30.1234567';
SELECT 
  @thedatetime2 AS 'datetime2',
  CAST(@thedatetime2 AS datetime) AS 'datetime';

Result:

+-----------------------------+-------------------------+
| datetime2                   | datetime                |
|-----------------------------+-------------------------|
| 2025-05-21 10:15:30.1234567 | 2025-05-21 10:15:30.123 |
+-----------------------------+-------------------------+

Example 4 – Explicit Conversion using CONVERT()

Here’s an example of an explicit conversion using the CONVERT() function instead of CAST().

DECLARE @thedatetime2 datetime2;
SET @thedatetime2 = '2025-05-21 10:15:30.1234567';
SELECT 
  @thedatetime2 AS 'datetime2',
  CONVERT(datetime, @thedatetime2) AS 'datetime';

Result:

+-----------------------------+-------------------------+
| datetime2                   | datetime                |
|-----------------------------+-------------------------|
| 2025-05-21 10:15:30.1234567 | 2025-05-21 10:15:30.123 |
+-----------------------------+-------------------------+