Returns an expression converted to a supplied data type.
CONVERT ( data-type, expression [ , format-style ] )
For converting strings to date or time data types and vice versa, format-style is a style code number that describes the date format string to be used. Table 4-17 lists the meanings of the values of the format-style argument.
Without century (yy) | With century (yyyy) | Output |
---|---|---|
- | 0 or 100 | mmm dd yyyy hh:nnAM (or PM) |
1 | 101 | mm/dd/yy[yy] |
2 | 102 | [yy]yy.mm.dd |
3 | 103 | dd/mm/yy[yy] |
4 | 104 | dd.mm.yy[yy] |
5 | 105 | dd-mm-yy[yy] |
6 | 106 | dd mmm yy[yy] |
7 | 107 | mmm dd, yy[yy] |
8 | 108 | hh:nn:ss |
- | 9 or 109 | mmm dd yyyy hh:nn:ss:sssAM (or PM) |
10 | 110 | mm-dd-yy[yy] |
11 | 111 | [yy]yy/mm/dd |
12 | 112 | [yy]yymmdd |
13 | 113 | dd mmm yyyy hh:nn:ss:sss (24 hour clock, Europe default + milliseconds, 4-digit year) |
14 | 114 | hh:nn:ss (24 hour clock) |
20 | 120 | yyyy-mm-dd hh:nn:ss (24-hour clock, ODBC canonical, 4-digit year) |
21 | 121 | yyyy-mm-dd hh:nn:ss.sss (24 hour clock, ODBC canonical with milliseconds, 4-digit year) |
- | 365 | yyyyjjj (as a string or integer, where jjj is the Julian day number from 1 to 366 within the year) |
If no format-style argument is provided, style code 0 is used.
The following statements illustrate the use of format styles:
SELECT CONVERT( CHAR( 20 ), order_date, 104 ) FROM sales_order
order_date |
---|
16.03.1993 |
20.03.1993 |
23.03.1993 |
25.03.1993 |
... |
SELECT CONVERT( CHAR( 20 ), order_date, 7 ) FROM sales_order
order_date |
---|
mar 16, 93 |
mar 20, 93 |
mar 23, 93 |
mar 25, 93 |
... |
The following statements illustrate the use of the format style 365, which converts data of type DATE and DATETIME to and from either string or integer type data:
CREATE TABLE tab (date_col DATE, int_col INT, char7_col CHAR(7)); INSERT INTO tab (date_col, int_col, char7_col) VALUES (‘Dec 17, 2004’, 2004352, ‘2004352’);
SELECT CONVERT(VARCHAR(8), tab.date_col, 365) FROM tab;
returns ‘2004352’
SELECT CONVERT(INT, tab.date_col, 365) from tab;
returns 2004352
SELECT CONVERT(DATE, tab.int_col, 365) FROM TAB;
returns 2004-12-17
SELECT CONVERT(DATE, tab.char7_col, 365) FROM tab;
returns 2004-12-17
The following statement illustrates conversion to an integer, and returns the value 5.
SELECT CONVERT( integer, 5.2 ) FROM iq_dummy
The result data type of a CONVERT function is a LONG VARCHAR. If you use CONVERT in a SELECT INTO statement, you must have a Large Objects Management option license or use CAST and set CONVERT to the correct data type and size.
See
“REPLACE function [String]” for more information.
“CAST function [Data type conversion]”