convert

Description

Converts the specified value to another datatype or a different datetime display format.

Syntax

convert (datatype [(length) | (precision[, scale])] 
	[null | not null], expression [, style])

Parameters

Examples

Example 1

select title, convert(char(12), total_sales) 
from titles

Example 2

select title, total_sales 
from titles 
where convert(char(20), total_sales) like "1%"

Example 3

Converts the current date to style 3, dd/mm/yy:

select convert(char(12), getdate(), 3)

Example 4

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 

Example 5

Returns the integer equivalent of the string “0x00000100”. Results can vary from one platform to another:

select convert(integer, 0x00000100)

Example 6

Returns the platform-specific bit pattern as a Sybase binary type:

select convert (binary, 10)

Example 7

Returns 1, the bit string equivalent of $1.11:

select convert(bit, $1.11)

Example 8

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

Usage

• convert, a datatype conversion function, converts between a wide variety of datatypes and reformats date/time and money data for display purposes.

• If they are compressed, convert decompresses large object (LOB) columns before converting them to other datatypes.

• convert – returns the specified value, converted to another datatype or a different datetime display format. When converting from unitext to other character and binary datatypes, the result is limited to the maximum length of the destination datatype. If the length is not specified, the converted value has a default size of 30 bytes. If you are using enabled enable surrogate processing, a surrogate pair is returned as a whole. For example, this is what is returned if you convert a unitext column that contains data U+0041U+0042U+20acU+0043 (stands for “AB ı”) to a UTF-8 varchar(3) column:

  select convert(varchar(3), ut) from untable
  ---
  AB
  

• 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).

  The result is an undefined value if:

  • The expression being converted is to a not null result.

  • The expression’s value is null.

  Use the following select statement to generate a known non-NULL value for predictable results:

  select convert(int not null isnull(col2, 5)) from table1
  

• 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.

• You can use unichar expressions as a destination datatype or you can convert them to another datatype. unichar expressions can be converted either explicitly between any other datatype supported by the server, or implicitly.

• If you do not specify the length 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, an error message appears.

Implicit conversion between types when the primary fields do not match may cause 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 is truncated, leaving only the date portion. If a time value is converted to a datetime value, a default date portion of Jan 1, 1900 is 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 is added to the datetime value.

Example 9

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 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.

Example 10

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.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute convert.

See also