Home » Convert Datetime To String

Convert Datetime To String

Summary: in this tutorial, you will learn how to convert datetime to string in SQL Server using the CONVERT() function.

Using the CONVERT() function to convert datetime to string

To convert a datetime to a string, you use the CONVERT() function as follows:

CONVERT(VARCHAR, datetime [,style])Code language: SQL (Structured Query Language) (sql)

In this syntax:

  • VARCHAR is the first argument that represents the string type.
  • datetime is an expression that evaluates to date or datetime value that you want to convert to a string
  • sytle specifies the format of the date. The value of style is a number predefined by SQL Server. The style parameter is optional.

The following table illustrates the valid style and the corresponding format of the datetime after converting to a string.

Without century (yy)With century (yyyy)StandardFormat
–0 or 100Default for datetime and smalldatetimemon dd yyyy hh:miAM (or PM)
1101U.S.1 = mm/dd/yy
101 = mm/dd/yyyy
2102ANSI2 = yy.mm.dd
102 = yyyy.mm.dd
3103British/French3 = dd/mm/yy
103 = dd/mm/yyyy
4104German4 = dd.mm.yy
104 = dd.mm.yyyy
5105Italian5 = dd-mm-yy
105 = dd-mm-yyyy
6106–6 = dd mon yy
106 = dd mon yyyy
7107–7 = Mon dd, yy
107 = Mon dd, yyyy
8108–hh:mi:ss
–9 or 109Default + millisecondsmon dd yyyy hh:mi:ss:mmmAM (or PM)
10110USA10 = mm-dd-yy
110 = mm-dd-yyyy
11111JAPAN11 = yy/mm/dd
111 = yyyy/mm/dd
12112ISO12 = yymmdd
112 = yyyymmdd
–13 or 113Europe default + millisecondsdd mon yyyy hh:mi:ss:mmm(24h)
14114–hh:mi:ss:mmm(24h)
–20 or 120ODBC canonicalyyyy-mm-dd hh:mi:ss(24h)
–21 or 121ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffsetyyyy-mm-dd hh:mi:ss.mmm(24h)
–126ISO8601yyyy-mm-ddThh:mi:ss.mmm (no spaces)
–127ISO8601 with time zone Z.yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
–130Hijridd mon yyyy hh:mi:ss:mmmAM
–131Hijridd/mm/yyyy hh:mi:ss:mmmAM

Converting a datetime to a string examples

1) Convert datetime to string in mon dd yyyy hh:miAM (or PM) format example

DECLARE @dt DATETIME = '2019-12-31 14:43:35.863';

SELECT 
    CONVERT(VARCHAR(20),@dt,0) s1,
    CONVERT(VARCHAR(20),@dt,100) s2;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

s1                   s2
-------------------- --------------------
Dec 31 2019  2:43PM  Dec 31 2019  2:43PM

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

2) Convert datetime to string in mm/dd/yy and mm/dd/yyyy formats example

DECLARE @dt DATETIME = '2019-12-31 14:43:35.863';
SELECT 
    CONVERT(VARCHAR(10),@dt,1) s1,
    CONVERT(VARCHAR(10),@dt,101) s2;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

s1         s2
---------- ----------
12/31/19   12/31/2019

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

3) Convert datetime to string in mon dd yyyy hh:miAM (or PM) format example

DECLARE @dt DATETIME = '2019-12-31 14:43:35.863';

SELECT 
    CONVERT(VARCHAR(10),@dt,3) s1,
    CONVERT(VARCHAR(10),@dt,103) s2;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

s1         s2
---------- ----------
31/12/19   31/12/2019

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

4) Convert datetime to string in dd.mm.yy and dd.mm.yyyy formats example

DECLARE @dt DATETIME = '2019-12-31 14:43:35.863';

SELECT 
    CONVERT(VARCHAR(10),@dt,4) s1,
    CONVERT(VARCHAR(10),@dt,104) s2;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

s1         s2
---------- ----------
31.12.19   31.12.2019

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

5) Convert datetime to string in dd-mm-yy dd-mm-yyyy formats example

DECLARE @dt DATETIME = '2019-12-31 14:43:35.863';

SELECT 
    CONVERT(VARCHAR(10),@dt,5) s1,
    CONVERT(VARCHAR(10),@dt,105) s2;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

s1         s2
---------- ----------
31-12-19   31-12-2019

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

6) Convert datetime to string in dd mon yy dd mon yyyy formats example

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';

SELECT 
    CONVERT(VARCHAR(10), @dt, 6) s1, 
    CONVERT(VARCHAR(10), @dt, 106) s2;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

s1         s2
---------- ----------
31 Dec 19  31 Dec 201

(1 row affected)       
Code language: SQL (Structured Query Language) (sql)

7) Convert datetime to string in Mon dd, yy Mon dd, yyyy formats example

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(10), @dt, 7) s1, 
       CONVERT(VARCHAR(10), @dt, 107) s2;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

s1         s2
---------- ----------
Dec 31, 19 Dec 31, 20

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

8) Convert datetime to string in hh:mi:ss format example

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(10), @dt, 108) s1;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

s1
----------
14:43:35

(1 row affected) 
Code language: SQL (Structured Query Language) (sql)

9) Convert datetime to string in mon dd yyyy hh:mi:ss:mmmAM (or PM) format example

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT 
    CONVERT(VARCHAR(30), @dt, 109) s1;    
Code language: SQL (Structured Query Language) (sql)

Here is the output:

s1
------------------------------
Dec 31 2019  2:43:35:863PM

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

10) Convert datetime to string in mm-dd-yy and mm-dd-yyyy format example

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(10), @dt, 10) s1, 
    CONVERT(VARCHAR(10), @dt, 110) s2;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

s1         s2
---------- ----------
12-31-19   12-31-2019

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

11) Convert datetime to string in yy/mm/dd and yyyy/mm/dd format example

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(10), @dt, 11) s1, 
    CONVERT(VARCHAR(10), @dt, 111) s2;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

s1         s2
---------- ----------
19/12/31   2019/12/31

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

12) Convert datetime to string in yymmdd and yyyymmdd format example

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(10), @dt, 12) s1, 
    CONVERT(VARCHAR(10), @dt, 112) s2;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

s1         s2
---------- ----------
191231     20191231

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

13) Convert datetime to string in dd mon yyyy hh:mi:ss:mmm(24h) format example

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(10), @dt, 113) s1;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

s1
----------
31 Dec 201

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

14) Convert datetime to string in hh:mi:ss:mmm(24h) format example

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(20), @dt, 114) s1;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

s1
--------------------
14:43:35:863

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

15) Convert datetime to string in yyyy-mm-dd hh:mi:ss(24h) format example

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(20), @dt, 120) s1;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

s1
--------------------
2019-12-31 14:43:35

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

16) Convert datetime to string in yyyy-mm-dd hh:mi:ss.mmm(24h) format example

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(30), @dt, 121) s1;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

s1
------------------------------
2019-12-31 14:43:35.863

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

17) Convert datetime to string in yyyy-mm-ddThh:mi:ss.mmm format example

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(25), @dt, 126) s1;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

s1
-------------------------
2019-12-31T14:43:35.863

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

18) Convert datetime to string in yyyy-mm-ddThh:mi:ss.mmmZ format example

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(25), @dt, 127) s1;
Code language: SQL (Structured Query Language) (sql)

Here is the result set:

s1
-------------------------
2019-12-31T14:43:35.863

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use convert a date to a string using the CONVERT() function.

Was this tutorial helpful?
SQL Server TRY_PARSE Function
Previously
SQL Server TRY_PARSE Function
Up Next
Convert String to Datetime
Convert String to Datetime