CONVERT Function [Data Type Conversion]

Syntax

CONVERT ( data-type, expression [ , format-style ] )

Parameters

Parameter	Description
data-type	The data type to which the expression is converted.
expression	The expression to be converted.
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.

If no format-style argument is provided, the database option settings are used.

CONVERT format style code output
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 or 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 or 120	yyyy-mm-dd hh:nn:ss (24-hour clock, ODBC canonical, 4-digit year)
-	21 or 121	yyyy-mm-dd hh:nn:ss.sss (24 hour clock, ODBC canonical with milliseconds, 4-digit year)
36	136	hh:nn:ss.ssssssAM (or PM)
37	137	hh:nn:ss.ssssss
38	138	mmm dd yy[yy] hh:nn:ss.ssssssAM (or PM)
39	139	mmm dd yy[yy] hh:nn:ss.ssssss
40	140	[yy]yy-mm-dd hh:nn:ss.ssssss
-	365	yyyyjjj (as a string or integer, where jjj is the Julian day number from 1 to 366 within the year)

Abbreviations and values for date parts in the CONVERT format style table:

Abbreviation	Date part	Values
hh	hour	0 – 23
nn	minute	0 – 59
ss	second	0 – 59
sss	millisecond	0 – 999
ssssss	microsecond	0 – 999999
mmm	month	Jan to Dec
dd	day	1 – 31
yyyy	year	0001 – 9999
mm	month	1 – 12

Returns

The data type specified.

Remarks

The result data type of a CONVERT function is a LONG VARCHAR. If you use CONVERT in a SELECT INTO statement, you must have an Unstructured Data Analytics Option license or use CAST and set CONVERT to the correct data type and size.

Standards and Compatibility

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Compatible with Adaptive Server and SQL Anywhere, except for format style 365, which is an SAP Sybase IQ -only extension.

Example

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
...

SELECT order_datetime, CONVERT(CHAR(30), order_datetime, 40)
order_datetime40,
CONVERT(CHAR(30), order_datetime, 140) order_datetime140
FROM sales_order;

order_datetime	order_datetime40	order_datetime140
03/05/2009 01:03.05.123456	09-03-05 01:03:05.123456	2009-03-05 01:03:05.123456
03/05/2009 13:05.07.654321	09-03-05 13:05:07.654321	2009-03-05 13:05:07.654321

SELECT CONVERT(CHAR(50), DATETIME('2009-11-03 11:10:42.033189'), 136) FROM iq_dummy returns 11:10:42.033189AM

SELECT CONVERT(CHAR(50), NOW(), 137) FROM iq_dummy returns 14:54:48.794122

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