This article contains examples of converting a datetime value to a datetime2 value in SQL Server.

When you convert a datetime value to datetime2, the resulting value will depend on the fractional seconds precision that you assign to datetime2.

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

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

Example 1 – Implicit Conversion

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

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

Result:

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

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 datetime value to a datetime2 variable.

We can see that the datetime2 variable has more fractional seconds precision, and we end up with a fractional part of 1233333 (vs 123 for the datetime value).

Example 2 – 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. As you might expect, this can cause a lot of confusion if you’re not aware of how it works. Not only can it cause confusion when using datetime by itself, it can cause extra confusion when converting that value to another data type.

Here’s an example that demonstrates what I mean.

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

Result:

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

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

The datetime2 value however, set the fractional seconds to 1266667.

By the way, if we’d simply set the initial value to datetime2 in the first place, its fractional part would’ve returned 1250000.

Example 3 – Precision/Accuracy

One of the benefits of datetime2 is that it allows you to specify the fractional seconds precision. If you don’t do this, it uses 7 (therefore, the previous example uses 7).

We can therefore modify the previous example so that the datetime2 value uses the same fractional seconds precision as the datetime data type.

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

Result:

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

When we do this, the datetime2 value returns the same result as the datetime value. It also uses the same amount of storage (8 bytes). In this case, datetime2 uses 7 bytes to store the value, and 1 byte to store the precision of the value.

You can even specify less fractional seconds precision than datetime if you have no need for the extra precision. Doing this will save you a whole byte on storage space (your datetime2 value will use 7 bytes, compared to 8 bytes for the datetime value).

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

Result:

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

Bear in mind that this may also result in fractional seconds being rounded.

Example 4 – 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 datetime and datetime2.

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

Result:

+-------------------------+-------------------------+
| datetime                | datetime2(3)            |
|-------------------------+-------------------------|
| 2025-05-21 10:15:30.127 | 2025-05-21 10:15:30.127 |
+-------------------------+-------------------------+

Example 5 – Explicit Conversion using CONVERT()

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

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

Result:

+-------------------------+-------------------------+
| datetime                | datetime2(3)            |
|-------------------------+-------------------------|
| 2025-05-21 10:15:30.127 | 2025-05-21 10:15:30.127 |
+-------------------------+-------------------------+

Microsoft’s Recommendation

Microsoft recommends using explicit casting whenever a mixed comparison scenario between these two data types exist:

Under database compatibility level 130, implicit conversions from datetime to datetime2 data types show improved accuracy by accounting for the fractional milliseconds, resulting in different converted values… Use explicit casting to datetime2 datatype whenever a mixed comparison scenario between datetime and datetime2 datatypes exists.