The style parameter of convert provides a variety of date display formats for converting datetime or smalldatetime data to char or varchar.
The number argument you supply as the style parameter determines how the data appears. The year can use either two or four digits. Add 100 to a style value to get a 4-digit year, including the century (yyyy).
The table below shows the possible values for style, and the variety of date formats you can use. When you use style with smalldatetime, the styles that include seconds or milliseconds show zeros in those positions.
In the table, “mon” indicates a month spelled out, “mm” the month number or minutes. “HH ”indicates a 24-hour clock value, “hh” a 12-hour clock value. The last row, 23, includes a literal “T” to separate the date and time portions of the format.
|
Without Century (yy) |
With Century (yyyy) |
Standard |
Output |
|---|---|---|---|
|
- |
0 or 100 |
Default |
mon dd yyyy hh:mm AM (or PM) |
|
1 |
101 |
USA |
mm/dd/yy |
|
2 |
2 |
SQL standard |
yy.mm.dd |
|
3 |
103 |
English/French |
dd/mm/yy |
|
4 |
104 |
German |
dd.mm.yy |
|
5 |
105 |
dd-mm-yy |
|
|
6 |
106 |
dd mon yy |
|
|
7 |
107 |
mon dd, yy |
|
|
8 |
108 |
HH:mm:ss |
|
|
- |
9 or 109 |
Default + milliseconds |
mon dd yyyy hh:mm:sss AM (or PM) |
|
10 |
110 |
USA |
mm-dd-yy |
|
11 |
111 |
Japan |
yy/mm/dd |
|
12 |
112 |
ISO |
yymmdd |
|
13 |
113 |
yy/dd/mm |
|
|
14 |
114 |
mm/yy/dd |
|
|
15 |
115 |
dd/yy/mm |
|
|
- |
16 or 116 |
mon dd yyyy HH:mm:ss |
|
|
17 |
117 |
hh:mmAM |
|
|
18 |
118 |
HH:mm |
|
|
19 |
hh:mm:ss:zzzAM |
||
|
20 |
HH:mm:ss:zzz |
||
|
21 |
yy/mm/dd HH:mm:ss |
||
|
22 |
yy/mm/dd hh:mm AM (or PM) |
||
|
23 |
yyyy-mm-ddTHH:mm:ss |
The default values, style 0 or 100, and 9 or 109, always return the century (yyyy).
This example converts the current date to style 3, dd/mm/yy:
select convert(char(12), getdate(), 3)
When converting date data to a character type, use style numbers 1 – 7 (101 – 107) or 10 – 12 (110 – 112) to specify the display format. The default value is 100 (mon dd yyyy hh:miAM (or PM )). If date data is converted to a style that contains a time portion, that time portion reflects the default value of zero.When converting time data to a character type, use style number 8 or 9 (108 or 109) to specify the display format. The default is 100 (mon dd yyyy hh:miAM (or PM)). If time data is converted to a style that contains a date portion, the default date of Jan 1, 1900 appears.
select convert(datetime, "01/01/01") ------------
Jan 1 2001 12:00AM
select convert(datetime, "01/01/01", NULL) ------------
Jan 1 2001 12:00AM