http://www.w3school.com.cn/sql/func_convert.asp Definition and usage
The CONVERT () function is a general function that converts a date to a new data type.
The CONVERT () function can display date/time data in different formats.
Grammar
CONVERT (data_type (length),data_to_be_converted,style)
data_type (length) Specifies the target data type (with optional lengths). The data_to_be_converted contains values that need to be converted. style Specifies the date/time output format.
The style value that can be used:
Style ID |
Style Format |
100 or 0 |
Mon dd yyyy hh:miam (or PM) |
101 |
Mm/dd/yy |
102 |
Yy.mm.dd |
103 |
Dd/mm/yy |
104 |
Dd.mm.yy |
105 |
Dd-mm-yy |
106 |
DD Mon yy |
107 |
Mon DD, yy |
108 |
Hh:mm:ss |
109 or 9 |
Mon dd yyyy hh:mi:ss:mmmAM (or PM) |
110 |
Mm-dd-yy |
111 |
Yy/mm/dd |
112 |
Yymmdd |
113 or 13 |
DD Mon yyyy hh:mm:ss:mmm (24h) |
114 |
Hh:mi:ss:mmm (24h) |
120 or 20 |
Yyyy-mm-dd Hh:mi:ss (24h) |
121 or 21 |
Yyyy-mm-dd hh:mi:ss.mmm (24h) |
126 |
Yyyy-mm-ddthh:mm:ss.mmm (no spaces) |
130 |
DD Mon yyyy hh:mi:ss:mmmAM |
131 |
Dd/mm/yy Hh:mi:ss:mmmAM |
Instance
The following script uses the CONVERT () function to display different formats. We will use the GETDATE () function to get the current date/time:
CONVERT (varchar), GETDATE ()) CONVERT (varchar (), GETDATE (), () CONVERT (varchar (one), GETDATE (), 106) CONVERT ( VARCHAR (in), GETDATE (), 113)
The results are similar:
Dec 11:45 pm12-29-200829 Dec 0829 Dec 2008 16:25:46.635
SQL Server CONVERT () function