Definition and Usage The CONVERT() function is a general function that converts an expression of one data type to another. The CONVERT() function can be used to display date/time data in different formats. Syntax CONVERT(data_type(length),expression,style) [TABLE="class: reference notranslate, width: 821"] [TR="bgcolor: #F6F4F0"] [/TR] data_type(length) Specifies the target data type (with an optional length) [TR="bgcolor: #F6F4F0"] expression Specifies the value to be converted [/TR] style Specifies the output format for the date/time [/TABLE] The table below represent the style values for datetime or smalldatetime conversion to character data: [TABLE="class: reference notranslate, width: 821"] [TR="bgcolor: #F6F4F0"] [/TR] - 0 or 100 mon dd yyyy hh:miAM (or PM) Default [TR="bgcolor: #F6F4F0"] 1 101 mm/dd/yy USA [/TR] 2 102 yy.mm.dd ANSI [TR="bgcolor: #F6F4F0"] 3 103 dd/mm/yy British/French [/TR] 4 104 dd.mm.yy German [TR="bgcolor: #F6F4F0"] 5 105 dd-mm-yy Italian [/TR] 6 106 dd mon yy [TR="bgcolor: #F6F4F0"] 7 107 Mon dd, yy [/TR] 8 108 hh:mm:ss [TR="bgcolor: #F6F4F0"] - 9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM) Default+millisec [/TR] 10 110 mm-dd-yy USA [TR="bgcolor: #F6F4F0"] 11 111 yy/mm/dd Japan [/TR] 12 112 yymmdd ISO [TR="bgcolor: #F6F4F0"] - 13 or 113 dd mon yyyy hh:mi:ss:mmm (24h) [/TR] 14 114 hh:mi:ss:mmm (24h) [TR="bgcolor: #F6F4F0"] - 20 or 120 yyyy-mm-dd hh:mi:ss (24h) [/TR] - 21 or 121 yyyy-mm-dd hh:mi:ss.mmm (24h) [TR="bgcolor: #F6F4F0"] - 126 yyyy-mm-ddThh:mi:ss.mmm (no spaces) ISO8601 [/TR] - 130 dd mon yyyy hh:mi:ss:mmmAM Hijiri [TR="bgcolor: #F6F4F0"] - 131 dd/mm/yy hh:mi:ss:mmmAM Hijiri [/TR] [/TABLE] [HR][/HR]Example 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(19),GETDATE()) CONVERT(VARCHAR(10),GETDATE(),10) CONVERT(VARCHAR(10),GETDATE(),110) CONVERT(VARCHAR(11),GETDATE(),6) CONVERT(VARCHAR(11),GETDATE(),106) CONVERT(VARCHAR(24),GETDATE(),113) The result would look something like this: Nov 04 2011 11:45 PM 11-04-11 11-04-2011 04 Nov 11 04 Nov 2011 04 Nov 2011 11:45:34:243
