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

When you convert a date value to datetime2, extra information is added to the value. This is because the datetime2 data type contains both date and time information. The date data type, on the other hand, only contains date information.

The datetime2 data type is basically an extension of the datetime data type. It has a larger date range, a larger default fractional precision, and optional user-specified precision.

In any case, the conversion process is exactly the same regardless of the data type. The only difference is the amount of information that’s available between date, datetime and datetime2.

Example 1 – Implicit Conversion

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

DECLARE @thedate date, @thedatetime2 datetime2
SET @thedate = '2020-12-01'
SET @thedatetime2 = @thedate
SELECT 
  @thedate AS 'date',
  @thedatetime2 AS 'datetime2';

Result:

+------------+-----------------------------+
| date       | datetime2                   |
|------------+-----------------------------|
| 2020-12-01 | 2020-12-01 00:00:00.0000000 |
+------------+-----------------------------+

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

We can see that the date variable only contains date information, whereas, the datetime2 variable contains both date and time information.

Furthermore, we get more time information than we would get if we converted it to the datetime data type.

When you convert between date and datetime2 using the default precision (7), the time component is set to 00:00:00.0000000 (compared to 00:00:00.000 for datetime). You can reduce the precision if you prefer (see below). Reducing the precision can also reduce the amount of space required to store the value.

The reason the time component is all zeros is because the date value doesn’t contain any time information, so there’s no way for SQL Server to know what time you want (if any).

Example 2 – Modify the Time

If you need to specify a time (but keep the same date), you can use the DATEADD() function to do just that.

DECLARE @thedate date, @thedatetime2 datetime2
SET @thedate = '2020-12-01'
SET @thedatetime2 = @thedate
SET @thedatetime2 = DATEADD(hour, 8, @thedatetime2)
SELECT 
  @thedate AS 'date',
  @thedatetime2 AS 'datetime2';

Result:

+------------+-----------------------------+
| date       | datetime2                   |
|------------+-----------------------------|
| 2020-12-01 | 2020-12-01 08:00:00.0000000 |
+------------+-----------------------------+

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 date and datetime2.

DECLARE @thedate date
SET @thedate = '2020-12-01'
SELECT 
  @thedate AS 'date',
  CAST(@thedate AS datetime2) AS 'datetime2';

Result:

+------------+-----------------------------+
| date       | datetime2                   |
|------------+-----------------------------|
| 2020-12-01 | 2020-12-01 00:00:00.0000000 |
+------------+-----------------------------+

So we get the same result as the implicit conversion.

We can also adjust the time like this:

DECLARE @thedate date
SET @thedate = '2020-12-01'
SELECT 
  @thedate AS 'date',
  DATEADD(hour, 8, CAST(@thedate AS datetime2)) AS 'datetime2';

Result:

+------------+-----------------------------+
| date       | datetime2                   |
|------------+-----------------------------|
| 2020-12-01 | 2020-12-01 08:00:00.0000000 |
+------------+-----------------------------+

Example 4 – Explicit Conversion using CONVERT()

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

DECLARE @thedate date
SET @thedate = '2020-12-01'
SELECT 
  @thedate AS 'date',
  CONVERT(datetime2, @thedate) AS 'datetime2';

Result:

+------------+-----------------------------+
| date       | datetime2                   |
|------------+-----------------------------|
| 2020-12-01 | 2020-12-01 00:00:00.0000000 |
+------------+-----------------------------+

And adjusting the time:

DECLARE @thedate date
SET @thedate = '2020-12-01'
SELECT 
  @thedate AS 'date',
  DATEADD(hour, 8, CONVERT(datetime2, @thedate)) AS 'datetime2';

Result:

+------------+-----------------------------+
| date       | datetime2                   |
|------------+-----------------------------|
| 2020-12-01 | 2020-12-01 08:00:00.0000000 |
+------------+-----------------------------+

Example 5 – Precision

The datetime2 data type allows you to specify the precision (up to the default 7). In other words, you don’t need to use the full 7 digits if you don’t need to.

Example:

DECLARE @thedate date
SET @thedate = '2020-12-01'
SELECT 
  @thedate AS 'date',
  CONVERT(datetime2(2), @thedate) AS 'datetime2(2)',
  CONVERT(datetime2(4), @thedate) AS 'datetime2(4)';

Result:

+------------+------------------------+--------------------------+
| date       | datetime2(2)           | datetime2(4)             |
|------------+------------------------+--------------------------|
| 2020-12-01 | 2020-12-01 00:00:00.00 | 2020-12-01 00:00:00.0000 |
+------------+------------------------+--------------------------+

One benefit of reducing the precision is that it can also reduce the amount of space required to store the value. In particular, 6 bytes for precision less than 3, 7 bytes for precision 3 or 4, and all other precision require 8 bytes. However, note that the first byte is used to store the precision, so the actual value is the storage size indicated here plus 1 additional byte to store the precision.