SQL - Date/Time to String Conversion by using Convert ( ) function

In this blog, we will learn how to convert the date/time into string by using Convert () SQL function and explore the different style of date/time format in conversion.

Convert () is used to convert a given value of one data type to another data type and it works same as Cast () function except it allows third parameter for date /time format style eg. yyyy-mm-dd, dd mm yyyy etc.

Here is an example for Converting from datetime with default style (0, 100) and without passing style options and all three return same output (mon dd yyyy hh:miAM (or PM))

Select Convert(varchar,getdate())

Select Convert(varchar,getdate(), 100)

Select Convert(varchar,getdate(), 0)

Output:

Jul 31 2019  4:18PM

Two Digit Years Style (yy):

The below sql script convert the datetime into date string with 2 digits year part

Select Convert(varchar,getdate(), 1)

Output:

07/31/19

Select Convert(varchar,getdate(), 2)

Output:

19.07.31

Below is a list of formats and an output

Style /Format	Output
1	mm/dd/yy
2	yy.mm.dd
3	dd/mm/yy
4	dd.mm.yy
5	dd-mm-yy
6	dd mon yy
7	Mon dd, yy
8	hh:mi:ss
10	mm-dd-yy
11	yy/mm/dd
12	yymmdd
14	hh:mi:ss:mmm

Four Digit Years Style (yyyy):

The below sql script convert the datetime to date string with 4 digits year part

Select Convert(varchar,getdate(), 101)

Output:

07/31/2019

Select Convert(varchar,getdate(), 102)

Output:

2019.07.31

Below is a list of formats and an output

Style /Format	Output
100	mon dd yyyy hh:miAM
101	mm/dd/yyyy
102	yyyy.mm.dd
103	dd/mm/yyyy
104	dd.mm.yyyy
105	dd-mm-yyyy
106	dd mon yyyy
107	Mon dd, yyyy
108	hh:mi:ss
109	mon dd yyyy hh:mi:ss:mmmAM (or PM)
110	mm-dd-yyyy
111	yyyy/mm/dd
112	yyyymmdd
113	dd mon yyyy hh:mi:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
120	yyyy-mm-dd hh:mi:ss(24h)
121	yyyy-mm-dd hh:mi:ss.mmm(24h)