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

The obvious consequence of converting a datetime value to date is that you lose the time portion. However, one benefit is that you reduce the storage size from 8 bytes down to 3 bytes. Either way, you would only do this conversion if you don’t need the time portion.

The datetime data type includes the date and time, with a 3 digit fractional seconds part. Its accuracy is rounded to increments of .000, .003, or .007 seconds.

However, the date data type has an accuracy of 1 day (and it doesn’t include the time, as mentioned).

Example 1 – Implicit Conversion

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

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

Result:

+-------------------------+------------+
| datetime                | date       |
|-------------------------+------------|
| 2025-05-21 10:15:30.123 | 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 datetime value to a date variable.

In this example we can see that the date value only includes the date (without the time component).

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

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

Result:

+-------------------------+------------+
| datetime                | date       |
|-------------------------+------------|
| 2025-05-21 10:15:30.123 | 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 @thedatetime datetime;
SET @thedatetime = '2025-05-21 10:15:30.123';
SELECT 
  @thedatetime AS 'datetime',
  CONVERT(date, @thedatetime) AS 'date';

Result:

+-------------------------+------------+
| datetime                | date       |
|-------------------------+------------|
| 2025-05-21 10:15:30.123 | 2025-05-21 |
+-------------------------+------------+