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)
|
