User's Guide Part VI. SQL Anywhere Reference Chapter 42. Watcom-SQL FunctionsData type conversion functions convert values.
Data type conversion function:
CAST( expression AS datatype )
| CONVERT( datatype, expression [ , format-style ] )
The DATE, DATETIME, and DATEFORMAT functions which convert expressions to dates, timestamps, or strings based on a date format are listed in "Date and time functions". The STRING function, which converts expressions to a string, is discussed in "String functions".
SQL Anywhere carries out many type conversions automatically. For example, if a string is supplied where a numerical expression is required, the string is automatically converted to a number. For more information on automatic data type conversions carried out by SQL Anywhere, see "Data type conversions".
CAST( expression AS data-type ) Returns the value of expression, converted to the supplied data-type. If the length is omitted for character string types, SQL Anywhere chooses an appropriate length. If neither precision nor scale is specified for a DECIMAL conversion, SQL Anywhere selects appropriate values. For example:
CAST( '1992-10-31' AS DATE ) --ensure string is used--as a DATE
CAST( 1 + 2 AS CHAR ) --SQL Anywhere chooses--length
CAST( Surname AS CHAR(10) ) --useful for shortening--strings
CONVERT( data-type, expression ) Returns the expression converted to data-type. The following is an example of this use of CONVERT:
SELECT CONVERT (VARCHAR(12), order_date )FROM sales_order ;
CONVERT( data-type, expression, format-style ) For converting strings to date or time data types and vice versa, the format-style is a style code number describing the date format string to be used. The values of the format-style argument have the following meanings:
| (yy) | (yyyy) | Output |
|---|---|---|
| - | -0 or 100 | mon dd yyyy hh:miAM (or PM) |
| 1 | 101 | mm/dd/yy |
| 2 | 102 | yy.mm.dd |
| 3 | 103 | dd/mm/yy |
| 4 | 104 | dd.mm.yy |
| 5 | 105 | dd-mm-yy |
| 6 | 106 | dd mon yy |
| 7 | 107 | mon dd, yy |
| 8 | 108 | hh:mm:ss |
| - | 9 or 109 | mmm dd yyyy hh:mi:ss:mmmAM (or PM) |
| 10 | 110 | mm-dd-yy |
| 11 | 111 | yy/mm/dd |
| 12 | 112 | yymmdd |
If no format-styleargument is provided, Style Code 0 is used.