convert

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.

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 )
Standards and compatibility
SQL/92     Vendor extension.

SQL/99     Vendor extension.

Sybase     Compatible with Adaptive Server Enterprise