Contents Index CONNECTION_PROPERTY function [System] COS function [Numeric]

ASA SQL Reference
  SQL Functions
    Alphabetical list of functions

CONVERT function [Data type conversion]

Function 

Returns an expression converted to a supplied data type.

Syntax 

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

Parameters 

data type    The data type to which the expression will be converted.

expression    The expression to be converted.

format-style    For converting strings to date or time data types and vice versa, the format-style is a style code number that describes the date format string to be used. The values of the format-style argument have the following meanings:

Without century (yy) With century (yyyy) Output
- 0 or 100 Mmm dd yyyy hh:nn:ss:sss AM (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 yyy hh:nn:ss:sss (24 hour clock, Europe default + milliseconds, 4-digit year )
14 114 hh:nn:ss:sss (24 hour clock)
20 120 yyyy-mm-dd hh:nn:ss:sss (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 )

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

For a description of the styles produced by each output symbol (such as Mmm), see DATE_FORMAT option [compatibility].

Standards and compatibility 
See also 

CAST function [Data type conversion]

Example 

The following statements illustrate the use of format styles:

SELECT CONVERT( CHAR( 20 ), order_date, 104 )
FROM sales_order
order_date
16.03.2000
20.03.2000
23.03.2000
25.03.2000
... 
SELECT CONVERT( CHAR( 20 ), order_date, 7 )
FROM sales_order
order_date
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 )

Contents Index CONNECTION_PROPERTY function [System] COS function [Numeric]