SQL Server CONVERT() Function
 SQL Server Date Functions
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 |
SQL Server Date Functions
 |
|
|
See why there are 20,000+ Ektron integrations worldwide.
Request an INSTANT DEMO or download a FREE TRIAL today. |
|
|
|
