Returns the specified value, converted to another datatype or a different datetime display format.
convert (datatype [(length) | (precision[, scale])] [null | not null], expression [, style])
is the system-supplied datatype (for example, char(10), unichar (10), varbinary (50), or int) into which to convert the expression. You cannot use user-defined datatypes.
When Java is enabled in the database, datatype can also be a Java-SQL class in the current database.
is an optional parameter used with char, nchar, unichar, univarchar, varchar, nvarchar, binary and varbinary datatypes. If you do not supply a length, Adaptive Server truncates the data to 30 characters for the character types and 30 bytes for the binary types. The maximum allowable length for character and binary expression is 64K.
is the number of significant digits in a numeric or decimal datatype. For float datatypes, precision is the number of significant binary digits in the mantissa. If you do not supply a precision, Adaptive Server uses the default precision of 18 for numeric and decimal datatypes.
is the number of digits to the right of the decimal point in a numeric, or decimal datatype. If you do not supply a scale, Adaptive Server uses the default scale of 0.
specifies the nullabilty of the result expression. If you do not supply either null or not null, the converted result has the same nullability as the expression.
is the value to be converted from one datatype or date format to another.
When Java is enabled in the database, expression can be a value to be converted to a Java-SQL class.
When Unichar is used as the destination data type, the default length of 30 Unicode values is used if no length is specified.
is the display format to use for the converted data. When converting money or smallmoney data to a character type, use a style of 1 to display a comma after every 3 digits.
When converting datetime or smalldatetime data to a character type, use the style numbers in Table 2-6 to specify the display format. Values in the left-most column display 2-digit years (yy). For 4-digit years (yyyy), add 100, or use the value in the middle column.
When converting date data to a character type, use style numbers 1 through 7 (101 through 107) or 10 through 12 (110 through 112) in Table 4-4 to specify the display format. The default value is 100 (mon dd yyyy hh:miAM (or PM )). If date data is converted to a style that contains a time portion, that time portion will reflect the default value of zero.When converting time data to a character type, use style number 8 or 9 (108 or 109) to specify the display format. The default is 100 (mon dd yyyy hh:miAM (or PM)). If time data is converted to a style that contains a date portion, the default date of Jan 1, 1900 will be displayed.
Symbolic value |
Datatype |
Datetime |
Date |
Time |
|---|---|---|---|---|
N/A |
0 or 100 |
mm/dd/yyyy 00:00:PM |
mm/dd/yy |
00:00:00:000PM(AM) |
1 |
101 |
mm/dd/yyy |
mm/dd/yy |
|
2 |
102 |
yy/mm/dd |
yy/mm/dd |
|
3 |
103 |
dd/mm/yy |
dd/mm/yy |
|
4 |
104 |
dd.mm.yy |
dd.mm.yy |
|
5 |
105 |
dd-mm-yy |
dd-mm-yy |
|
6 |
106 |
dd mm yy |
dd mm yy |
|
7 |
107 |
mon dd, yy |
mon dd, yy |
|
8 |
108 |
hh:mm:ss |
hh:mm:ss |
|
9 |
109 |
mm dd yy hh:mm:ss:zzzAM |
mm dd yyyy |
hh:mm:ss:zzzAM(PM) |
10 |
110 |
mm-dd-yy |
mm-dd-yy |
|
11 |
111 |
yy/mm/dd |
yy/mm/dd |
|
12 |
112 |
yymmdd |
yymmdd |
|
13 |
113 |
yy/dd/mm |
yy/dd/mm |
|
14 |
114 |
mm/yy/dd |
mm/yy/dd |
|
15 |
115 |
dd/yy/mm |
dd/yy/mm |
|
16 |
116 |
mon dd yy hh:mm:ss |
mon dd yy |
hh:mm:ss |
17 |
117 |
hh:mmPM (AM) |
hh:mm:AM(PM) |
|
18 |
118 |
hh:mm |
hh:mm |
|
19 |
119 |
hh:mm:ss:zzzAM (PM) |
hh:mm:ss:zzzAM (PM |
|
20 |
200 |
hh:mm:ss:zzz |
hh:mm:ss:zzz |
The default values (style 0 or 100), and style 9 or 109 return the century (yyyy). When converting to char or varchar from smalldatetime, styles that include seconds or milliseconds show zeros in those positions.
select title, convert(char(12), total_sales) from titles
select title, total_sales from titles where convert(char(20), total_sales) like "1%"
Converts the current date to style “3”, dd/mm/yy:
select convert(char(12), getdate(), 3)
If the value pubdate can be null, you must use varchar rather than char, or errors may result:
select convert(varchar(12), pubdate, 3) from titles
Returns the integer equivalent of the string “0x00000100”. Results can vary from one platform to another:
select convert(integer, 0x00000100)
Returns the platform-specific bit pattern as a Sybase binary type:
select convert (binary, 10)
Returns 1, the bit string equivalent of $1.11:
select convert(bit, $1.11)
Creates #tempsales with total_sales of datatype char(100), and does not allow null values. Even if titles.total_sales was defined as allowing nulls, #tempsales is created with #tempsales.total_sales not allowing null values:
select title, convert (char(100) not null, total_sales) into #tempsales from titles
convert, a datatype conversion function, converts between a wide variety of datatypes and reformats date/time and money data for display purposes.
For more information about datatype conversion, see “Datatype conversion functions”.
convert() generates a domain error when the argument falls outside the range over which the function is defined. This should happen rarely.
Use null or not null to specify the nullability of a target column. Specifically, this can be used with select into to create a new table and change the datatype and nullability of existing columns in the source table (See Example 8, above).
You can use convert to convert an image column to binary or varbinary. You are limited to the maximum length of the binary datatypes, which is determined by the maximum column size for your server’s logical page size. If you do not specify the length, the converted value has a default length of 30 characters.
Unichar expressions can be used as a destination data type or they can be converted to another data type. Unichar expressions can be converted either explicitly between any other data type supported by the server, or implicitly.
If length is not specified when unichar is used as a destination type, the default length of 30 Unicode values is used. If the length of the destination type is not large enough to accommodate the given expression, as error message appears.
Implicit conversion between types when the primary fields do not match may cause either data truncation, the insertion of a default value, or an error message to be raised. For example, when a datetime value is converted to a date value, the time portion will be truncated leaving only the date portion. If a time value is converted to a datetime value, a default date portion of Jan 1, 1900 will be added to the new datetime value. If a date value is converted to a datetime value, a default time portion of 00:00:00:000 will be added to the datetime value.
DATE -> VARCHAR, CHAR, BINARY, VARBINARY, DATETIME, SMALLDATETIME TIME -> VARCHAR, CHAR, BINARY, VARBINARY, DATETIME, SMALLDATETIME VARCHAR, CHAR, BINARY, VARBINARY, DATETIME, SMALLDATETIME -> DATE VARCHAR, CHAR, BINARY, VARBINARY, DATETIME, SMALLDATETIME -> TIME
If the you attempt to explicitly convert a date to a datetime and the value is outside the datetime range such as "Jan 1, 1000" the conversion is not allowed and an informative error message is raised.
DATE -> UNICHAR, UNIVARCHAR TIME -> UNICHAR, UNIVARCHAR UNICHAR, UNIVARCHAR -> DATE UNICHAR, UNIVARCHAR -> TIME
When Java is enabled in the database, you can use convert to change datatypes in these ways:
Convert Java object types to SQL datatypes.
Convert SQL datatypes to Java types.
Convert any Java-SQL class installed in Adaptive Server to any other Java-SQL class installed in Adaptive Server if the compile-time datatype of the expression (the source class) is a subclass or superclass of the target class.
The result of the conversion is associated with the current database.
ANSI SQL – Compliance level: Transact-SQL extension.
Any user can execute convert.
Documents Java in Adaptive Server Enterprise for a list of allowed datatype mappings and more information about datatype conversions involving Java classes.
Datatypes User-defined datatypes
