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:

1	CONVERT(VARCHAR, datetime [,style])

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

1 2 3 4 5

DECLARE @dt DATETIME = '2019-12-31 14:43:35.863'; SELECT     CONVERT(VARCHAR(20),@dt,0) s1,     CONVERT(VARCHAR(20),@dt,100) s2;

Here is the output:

1 2 3 4 5

s1                   s2 -------------------- -------------------- Dec 31 2019  2:43PM  Dec 31 2019  2:43PM (1 row affected)

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

1 2 3 4

DECLARE @dt DATETIME = '2019-12-31 14:43:35.863'; SELECT     CONVERT(VARCHAR(10),@dt,1) s1,     CONVERT(VARCHAR(10),@dt,101) s2;

Here is the output:

1 2 3 4 5

s1         s2 ---------- ---------- 12/31/19   12/31/2019 (1 row affected)

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

1 2 3 4 5

DECLARE @dt DATETIME = '2019-12-31 14:43:35.863'; SELECT     CONVERT(VARCHAR(10),@dt,3) s1,     CONVERT(VARCHAR(10),@dt,103) s2;

Here is the output:

1 2 3 4 5

s1         s2 ---------- ---------- 31/12/19   31/12/2019 (1 row affected)

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

1 2 3 4 5

DECLARE @dt DATETIME = '2019-12-31 14:43:35.863'; SELECT     CONVERT(VARCHAR(10),@dt,4) s1,     CONVERT(VARCHAR(10),@dt,104) s2;

Here is the output:

1 2 3 4 5

s1         s2 ---------- ---------- 31.12.19   31.12.2019 (1 row affected)

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

1 2 3 4 5

DECLARE @dt DATETIME = '2019-12-31 14:43:35.863'; SELECT     CONVERT(VARCHAR(10),@dt,5) s1,     CONVERT(VARCHAR(10),@dt,105) s2;

Here is the output:

1 2 3 4 5

s1         s2 ---------- ---------- 31-12-19   31-12-2019 (1 row affected)

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

1 2 3 4 5

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863'; SELECT     CONVERT(VARCHAR(10), @dt, 6) s1,     CONVERT(VARCHAR(10), @dt, 106) s2;

Here is the output:

1 2 3 4 5

s1         s2 ---------- ---------- 31 Dec 19  31 Dec 201 (1 row affected)

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

1 2 3

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863'; SELECT CONVERT(VARCHAR(10), @dt, 7) s1,        CONVERT(VARCHAR(10), @dt, 107) s2;

Here is the output:

1 2 3 4 5

s1         s2 ---------- ---------- Dec 31, 19 Dec 31, 20 (1 row affected)

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

1 2	DECLARE @dt DATETIME= '2019-12-31 14:43:35.863'; SELECT CONVERT(VARCHAR(10), @dt, 108) s1;

Here is the output:

1 2 3 4 5

s1 ---------- 14:43:35 (1 row affected)

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

1 2 3

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863'; SELECT     CONVERT(VARCHAR(30), @dt, 109) s1;

Here is the output:

1 2 3 4 5

s1 ------------------------------ Dec 31 2019  2:43:35:863PM (1 row affected)

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

1 2 3

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863'; SELECT CONVERT(VARCHAR(10), @dt, 10) s1,     CONVERT(VARCHAR(10), @dt, 110) s2;

Here is the result set:

1 2 3 4 5

s1         s2 ---------- ---------- 12-31-19   12-31-2019 (1 row affected)

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

1 2 3

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863'; SELECT CONVERT(VARCHAR(10), @dt, 11) s1,     CONVERT(VARCHAR(10), @dt, 111) s2;

Here is the result set:

1 2 3 4 5

s1         s2 ---------- ---------- 19/12/31   2019/12/31 (1 row affected)

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

1 2 3

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863'; SELECT CONVERT(VARCHAR(10), @dt, 12) s1,     CONVERT(VARCHAR(10), @dt, 112) s2;

Here is the result set:

1 2 3 4 5

s1         s2 ---------- ---------- 191231     20191231 (1 row affected)

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

1 2	DECLARE @dt DATETIME= '2019-12-31 14:43:35.863'; SELECT CONVERT(VARCHAR(10), @dt, 113) s1;

Here is the result set:

1 2 3 4 5

s1 ---------- 31 Dec 201 (1 row affected)

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

1 2	DECLARE @dt DATETIME= '2019-12-31 14:43:35.863'; SELECT CONVERT(VARCHAR(20), @dt, 114) s1;

Here is the result set:

1 2 3 4 5

s1 -------------------- 14:43:35:863 (1 row affected)

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

1 2	DECLARE @dt DATETIME= '2019-12-31 14:43:35.863'; SELECT CONVERT(VARCHAR(20), @dt, 120) s1;

Here is the result set:

1 2 3 4 5

s1 -------------------- 2019-12-31 14:43:35 (1 row affected)

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

1 2	DECLARE @dt DATETIME= '2019-12-31 14:43:35.863'; SELECT CONVERT(VARCHAR(30), @dt, 121) s1;

Here is the result set:

1 2 3 4 5

s1 ------------------------------ 2019-12-31 14:43:35.863 (1 row affected)

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

1 2	DECLARE @dt DATETIME= '2019-12-31 14:43:35.863'; SELECT CONVERT(VARCHAR(25), @dt, 126) s1;

Here is the result set:

1 2 3 4 5

s1 ------------------------- 2019-12-31T14:43:35.863 (1 row affected)

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

1 2	DECLARE @dt DATETIME= '2019-12-31 14:43:35.863'; SELECT CONVERT(VARCHAR(25), @dt, 127) s1;

Here is the result set:

1 2 3 4 5

s1 ------------------------- 2019-12-31T14:43:35.863 (1 row affected)