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

When you convert a datetime2 value to a date data type, you lose the time portion. However, you also reduce the storage size from between 7 and 9 bytes down to 3 bytes. In any case, you would only do this conversion if you don’t need the time portion.

The datetime2 data type includes the date and time with a fractional seconds part between 0 and 7 (this depends on how many fractional seconds are assigned to it). Its accuracy is 100 nanoseconds.

The date data type on the other hand, doesn’t include the time, and it has an accuracy of 1 day.

Example 1 – Implicit Conversion

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

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

Result:

+-----------------------------+------------+
| datetime2                   | date       |
|-----------------------------+------------|
| 2025-05-21 10:15:30.1234567 | 2025-05-21 |
+-----------------------------+------------+

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 date variable.

Here we can see that the date value only includes the date (without the time component).

In this example, the datetime2 value uses the default precision (which results in 7 decimal places). This could be reduced to any number and the conversion result would be the same.

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

Result:

+---------------------+------------+
| datetime2           | date       |
|---------------------+------------|
| 2025-05-21 10:15:30 | 2025-05-21 |
+---------------------+------------+

Example 2 – 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 date.

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

Result:

+--------------------------+------------+
| datetime2                | date       |
|--------------------------+------------|
| 2025-05-21 10:15:30.1234 | 2025-05-21 |
+--------------------------+------------+

Example 3 – Explicit Conversion using CONVERT()

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

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

Result:

+--------------------------+------------+
| datetime2                | date       |
|--------------------------+------------|
| 2025-05-21 10:15:30.1234 | 2025-05-21 |
+--------------------------+------------+