Cast (expression as data_type (length))
Convert (data_type (length), expression, style) // If lengthis not specified, the default is 30 characters.
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.
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 |
For example:
cast (GetDate () as nvarchar) //The result is:1:38pm
convert (nvarchar, GetDate (), +) //Result: 06-17-2011
For details, please refer to: http://www.w3school.com.cn/sql/func_convert.asp
SQL Server Time type conversion function