How to Convert Between Date Formats in SQL Server using CAST()

In SQL Server, you can use use the CONVERT() function to convert a date value from one date data type to another (or between any other data type for that matter). However, that’s not the only function in the T-SQL toolbox for converting between data types.

The CAST() function is part of the ANSI SQL standard, and it does most of the things CONVERT() does. So in many cases, you have the option of which of these functions you prefer to use.

Many database professionals prefer CAST() due to the fact that it’s part of the ANSI SQL standard, however, others prefer CONVERT() due to the extra functionality that T-SQL’s implementation offers (such as being able to provide a date style).

In any case, this article provides examples of converting between different date formats using CAST().

Syntax

The syntax goes like this:

CAST ( expression AS data_type [ ( length ) ] )

These arguments are defined as follows:

expression: Any valid expression.
data_type: The target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used.
length: An optional integer that specifies the length of the target data type. The default value is 30.

Example 1 – Cast SYSDATETIME() as date

In this example, we generate the current date/time with the SYSDATETIME() function and cast the return value as a date data type.

Note that the SYSDATETIME() generates its value as a datetime2(7) data type, so we are converting it from that data type to another data type.

SELECT 
    SYSDATETIME() AS Original,
    CAST(SYSDATETIME() AS date) AS Converted;

Result:

+-----------------------------+-------------+
| Original                    | Converted   |
|-----------------------------+-------------|
| 2018-06-07 00:15:32.9265884 | 2018-06-07  |
+-----------------------------+-------------+

Example 2 – Cast SYSDATETIME() as smalldatetime

In this example, we cast the date as a smalldatetime data type.

SELECT 
    SYSDATETIME() AS Original,
    CAST(SYSDATETIME() AS smalldatetime) AS Converted;

Result:

+-----------------------------+---------------------+
| Original                    | Converted           |
|-----------------------------+---------------------|
| 2018-06-07 00:16:05.5142017 | 2018-06-07 00:16:00 |
+-----------------------------+---------------------+

Example 3 – Cast SYSDATETIME() as datetimeoffset

In this example, we cast the date as a datetimeoffset data type.

SELECT 
    SYSDATETIME() AS Original,
    CAST(SYSDATETIME() AS datetimeoffset) AS Converted;

Result:

+-----------------------------+------------------------------------+
| Original                    | Converted                          |
|-----------------------------+------------------------------------|
| 2018-06-07 10:19:23.9457462 | 2018-06-07 10:19:23.9457462 +00:00 |
+-----------------------------+------------------------------------+

Example 4 – Cast SYSDATETIME() as time

You aren’t limited to displaying the date component of the value. You can also cast it as a time data type, so that only the time component is returned. Like this:

SELECT 
    SYSDATETIME() AS Original,
    CAST(SYSDATETIME() AS time) AS Converted;

Result:

+-----------------------------+------------------+
| Original                    | Converted        |
|-----------------------------+------------------|
| 2018-06-07 00:20:21.5829364 | 00:20:21.5829364 |
+-----------------------------+------------------+

Example 5 – Cast SYSDATETIMEOFFSET() as date

Here’s an example using a different function to generate the original date/time value:

SELECT 
    SYSDATETIMEOFFSET() AS Original,
    CAST(SYSDATETIMEOFFSET() AS date) AS Converted;

Result:

+------------------------------------+----------------------+
| Original                           | Converted            |
|------------------------------------+----------------------|
| 2018-06-07 10:21:16.3617030 +10:00 | 2018-06-07           |
+------------------------------------+----------------------+

Example 6 – Converting from a Database Query

Here’s an example of converting the date that’s retrieved from a column in the WideWorldImporters sample database:

USE WideWorldImporters;
SELECT DISTINCT TOP 10 
    LastEditedWhen, 
    CAST(LastEditedWhen AS date) AS 'Converted'
FROM Sales.CustomerTransactions;

Result:

+-----------------------------+-------------+
| LastEditedWhen              | Converted   |
|-----------------------------+-------------|
| 2013-01-02 11:30:00.0000000 | 2013-01-02  |
| 2013-01-03 11:30:00.0000000 | 2013-01-03  |
| 2013-01-04 11:30:00.0000000 | 2013-01-04  |
| 2013-01-05 11:30:00.0000000 | 2013-01-05  |
| 2013-01-06 11:30:00.0000000 | 2013-01-06  |
| 2013-01-08 11:30:00.0000000 | 2013-01-08  |
| 2013-01-09 11:30:00.0000000 | 2013-01-09  |
| 2013-01-10 11:30:00.0000000 | 2013-01-10  |
| 2013-01-11 11:30:00.0000000 | 2013-01-11  |
| 2013-01-12 11:30:00.0000000 | 2013-01-12  |
+-----------------------------+-------------+

I’ve also written a post that shows how to convert between date formats using the CONVERT() function using the same examples as above.