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.
CONVERT( datatype, expression [ , format-style ] )
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 ) |
Depends on the data type specified.
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 date_format option.
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.
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.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |