CONVERT function [Data type conversion]

Returns an expression converted to a supplied data type.

The CAST, CONVERT, HEXTOINT, and INTTOHEX functions can be used to convert to and from hexadecimal values. For more information about using these functions, see Converting to and from hexadecimal values.

Syntax

CONVERT( datatype, expression [ , format-style ] )

Parameters

datatype The data type to which the expression is converted.
expression The expression to be converted.
format-style The style code to apply to the output value. Use this parameter when converting strings to date or time data types, and vice versa. The table below shows the supported style codes, followed by a representation of the output format produced by that style code. The style codes are separated into two columns, depending on whether the century is included in the output format (for example, 06 versus 2006).

Without century (yy) style codes	With century (yyyy) style codes	Output format
-	0 or 100	Mmm dd yyyy hh:nnAA
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:sssAA
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 or 114	hh:nn:ss:sss (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 )

Returns

Remarks

If no format-style argument is provided, style code 0 is used.

The CONVERT function can be used to convert a string to a DATE, TIME, or TIMESTAMP data type, provided that there is no ambiguity when parsing the string. If format-style is specified, the database server may use it as a hint on how to parse the string. An error is returned if the database server cannot parse the string unambiguously.

For information about the styles produced by each output symbol (such as Mmm), see UltraLite date_format creation parameter.

Standards and compatibility

SQL/2008 Vendor extension. The CONVERT function is defined in the SQL/2008 standard. However, in the SQL standard the purpose of CONVERT is to perform a transcoding of the input string expression to a different character set, which is implemented in SQL Anywhere as the CSCONVERT function.

Example

The following statements illustrate the use of format style.

SELECT CONVERT( CHAR( 20 ), OrderDate, 104 ) FROM SalesOrders;

OrderDate
16.03.2000
20.03.2000
23.03.2000
25.03.2000
...

SELECT CONVERT( CHAR( 20 ), OrderDate, 7 ) FROM SalesOrders;

OrderDate
Mar 16, 00
Mar 20, 00
Mar 23, 00
Mar 25, 00
...

The following statement illustrates conversion to an integer, and returns the value 5.

SELECT CONVERT( integer, 5.2 );

Discuss this page in DocCommentXchange.