Definition and Usage The CONVERT() function is a general function for converting data into a new data type. The CONVERT() function can be u...
Definition and Usage
The CONVERT() function is a general function for converting data into a new data type.The CONVERT() function can be used to display date/time data in different formats.
Syntax
CONVERT(data_type(length), data_to_be_converted,style) |
Where data_type(length) specifies the target data type (with an optional length), data_to_be_converted contains the value to be converted, and style specifies the output format for the date/time.
The styles that can be used are:
| 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 |
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(),110) CONVERT(VARCHAR(11),GETDATE(),106) CONVERT(VARCHAR(24),GETDATE(),113) |
The result would look something like this:
| Nov 04 2008 11:45 PM 11-04-2008 04 Nov 08 04 Nov 2008 11:45:34:243 |
