Convert Datetime to String in a Specified Format in SQL Server

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)	Standard	Format
–	0 or 100	Default for datetime and smalldatetime	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	1 = mm/dd/yy 101 = mm/dd/yyyy
2	102	ANSI	2 = yy.mm.dd 102 = yyyy.mm.dd
3	103	British/French	3 = dd/mm/yy 103 = dd/mm/yyyy
4	104	German	4 = dd.mm.yy 104 = dd.mm.yyyy
5	105	Italian	5 = dd-mm-yy 105 = dd-mm-yyyy
6	106	–	6 = dd mon yy 106 = dd mon yyyy
7	107	–	7 = Mon dd, yy 107 = Mon dd, yyyy
8	108	–	hh:mi:ss
–	9 or 109	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	10 = mm-dd-yy 110 = mm-dd-yyyy
11	111	JAPAN	11 = yy/mm/dd 111 = yyyy/mm/dd
12	112	ISO	12 = yymmdd 112 = yyyymmdd
–	13 or 113	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	–	hh:mi:ss:mmm(24h)
–	20 or 120	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
–	21 or 121	ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset	yyyy-mm-dd hh:mi:ss.mmm(24h)
–	126	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)
–	127	ISO8601 with time zone Z.	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
–	130	Hijri	dd mon yyyy hh:mi:ss:mmmAM
–	131	Hijri	dd/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?