How to Convert from One Date Format to Another in SQL Server using CONVERT()

Sometimes when working with databases and other programming environments, you get a date value but it’s in the wrong format/data type. For example, if a date has been generated with an inbuilt date function, it might include both the date and the time, right down to the last nanosecond. And all you want is the day, month, and year, say like this: 2018-01-01.

If this happens while you’re using SQL Server, you can use the CONVERT() function to convert it to another data type. When you do this, the data type will determine the format it appears as.

This article provides examples of using the CONVERT() function in SQL Server to convert a date value to another (date) data type.

Syntax

First, here’s how the official syntax goes:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

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.
style: An integer expression that specifies how the CONVERT() function will translate expression. For a style value of NULL, NULL is returned. data_type determines the range.

Example 1 – Convert from SYSDATETIME() to date Data Type

In this example, we generate the current date/time with the SYSDATETIME() function and convert that to 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,
    CONVERT(date, SYSDATETIME()) AS Converted;

Result:

+-----------------------------+-------------+
| Original                    | Converted   |
|-----------------------------+-------------|
| 2018-06-06 22:53:47.2318751 | 2018-06-06  |
+-----------------------------+-------------+

Example 2 – Convert from SYSDATETIME() to smalldatetime Data Type

In this example, we convert the date to a smalldatetime data type.

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

Result:

+-----------------------------+---------------------+
| Original                    | Converted           |
|-----------------------------+---------------------|
| 2018-06-06 22:56:51.6873250 | 2018-06-06 22:57:00 |
+-----------------------------+---------------------+

Example 3 – Convert from SYSDATETIME() to datetimeoffset Data Type

In this example, we convert the date to a datetimeoffset data type.

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

Result:

+-----------------------------+------------------------------------+
| Original                    | Converted                          |
|-----------------------------+------------------------------------|
| 2018-06-07 09:17:15.2410693 | 2018-06-07 09:17:15.2410693 +00:00 |
+-----------------------------+------------------------------------+

Example 4 – Convert from SYSDATETIME() to time Data Type

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

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

Result:

+-----------------------------+------------------+
| Original                    | Converted        |
|-----------------------------+------------------|
| 2018-06-06 23:01:41.7070775 | 23:01:41.7070775 |
+-----------------------------+------------------+

Example 5 – Convert from SYSDATETIMEOFFSET()

The previous examples all use the same inbuilt function to generate the date/time value, but of course, it doesn’t need to be generated by this one function. Here’s an example using the SYSDATETIMEOFFSET():

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

Result:

+------------------------------------+----------------------+
| Original                           | Converted            |
|------------------------------------+----------------------|
| 2018-06-07 09:12:27.3660685 +10:00 | 2018-06-07           |
+------------------------------------+----------------------+

Example 6 – Convert 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, 
    CONVERT(date, LastEditedWhen) 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  |
+-----------------------------+-------------+

Note that you’re not limited to just converting between two different date formats. If you have a date stored as a string for example, you can also use CONVERT() to convert from a string to a date, as well as any other data type you might need to convert to.

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