When using SQL Server, you can convert a date/time value into a string by using the CONVERT() function. This function allows you to convert between different data types.

In this article, we’ll be converting between various date/time data types to a varchar or nvarchar string.

One of the good things about this function is that it allows you to specify the style that the date will be returned in. For example, you can specify whether it’s returned as mm/dd/yyyy, yyyy.mm.dd, Mon dd, yyyy, etc. You can also specify whether the time component is returned and how it’s styled.

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.

Basic Example

In this example, we declare a variable and assign a value from the GETDATE() function. We then return the value, and we also convert that value to varchar and return that:

DECLARE @date datetime = GETDATE();
SELECT
    @date AS Original,
    CONVERT(varchar, @date) AS Converted;

Result:

+-------------------------+---------------------+
| Original                | Converted           |
|-------------------------+---------------------|
| 2018-06-07 03:08:21.997 | Jun  7 2018  3:08AM |
+-------------------------+---------------------+

The default length of the return data type is 30, so even though we only specified varchar, it will return the result as a varchar(30).

In this example, we didn’t provide a third argument to indicate the style that we want it returned in. Therefore, it has been converted using the default style for datetime and smalldatetime data types, which is 0 or 100 (more on styles below).

Specifying a Style

You can use a third argument to specify the style that the return value will take on:

DECLARE @date datetime = GETDATE();
SELECT
    @date AS Original,
    CONVERT(varchar(30), @date, 102) AS Converted;

Result:

+-------------------------+-------------+
| Original                | Converted   |
|-------------------------+-------------|
| 2018-06-07 03:42:33.840 | 2018.06.07  |
+-------------------------+-------------+

In this example, we specified a style of 102, which is the ANSI standard for displaying a date with a four digit year component.

To change this to an ANSI format with a two digit year, we can use a style of 2:

DECLARE @date datetime = GETDATE();
SELECT
    @date AS Original,
    CONVERT(varchar(30), @date, 2) AS Converted;

Result:

+-------------------------+-------------+
| Original                | Converted   |
|-------------------------+-------------|
| 2018-06-07 03:44:52.433 | 18.06.07    |
+-------------------------+-------------+

You can see more examples on CONVERT() from Date/Time to String Examples in SQL Server.