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

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

Example 1 – Implicit Conversion

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

DECLARE @thedate date, @thedatetime datetime
SET @thedate = '2020-12-01'
SET @thedatetime = @thedate
SELECT 
  @thedate AS 'date',
  @thedatetime AS 'datetime';

Result:

+------------+-------------------------+
| date       | datetime                |
|------------+-------------------------|
| 2020-12-01 | 2020-12-01 00:00:00.000 |
+------------+-------------------------+

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

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

When you convert between date and datetime, the time component is set to 00:00:00.000. This 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 change the time (but keep the same date), you can use the DATEADD() function to do just that.

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

Result:

+------------+-------------------------+
| date       | datetime                |
|------------+-------------------------|
| 2020-12-01 | 2020-12-01 08:00:00.000 |
+------------+-------------------------+

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

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

Result:

+------------+-------------------------+
| date       | datetime                |
|------------+-------------------------|
| 2020-12-01 | 2020-12-01 00:00:00.000 |
+------------+-------------------------+

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 datetime)) AS 'datetime';

Result:

+------------+-------------------------+
| date       | datetime                |
|------------+-------------------------|
| 2020-12-01 | 2020-12-01 08:00:00.000 |
+------------+-------------------------+

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(datetime, @thedate) AS 'datetime';

Result:

+------------+-------------------------+
| date       | datetime                |
|------------+-------------------------|
| 2020-12-01 | 2020-12-01 00:00:00.000 |
+------------+-------------------------+

And adjusting the time:

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

Result:

+------------+-------------------------+
| date       | datetime                |
|------------+-------------------------|
| 2020-12-01 | 2020-12-01 08:00:00.000 |
+------------+-------------------------+