Home

	Expand Search

Reference Manual

Chapter 2: Transact-SQL Functions

Datatype Conversion Functions

Datatype Conversion Functions

Datatype conversion functions change expressions from one datatype to another and specify new display formats for date/time information. The datatype conversion functions are:

• convert()
• inttohex()
• hextoint()

The datatype conversion functions can be used in the select list, in the where clause, and anywhere else an expression is allowed.

Adaptive Server performs certain datatype conversions automatically. These are called implicit conversions. For example, if you compare a char expression and a datetime expression, or a smallint expression and an int expression, or char expressions of different lengths, Adaptive Server automatically converts one datatype to another.

You must request other datatype conversions explicitly, using one of the built-in datatype conversion functions. For example, before concatenating numeric expressions, you must convert them to character expressions.

Adaptive Server does not allow you to convert certain datatypes to certain other datatypes, either implicitly or explicitly. For example, you cannot convert smallint data to datetime or datetime data to smallint. Unsupported conversions result in error messages.

Table 2-3 indicates whether individual datatype conversions are performed implicitly or explicitly or are unsupported.

Table 2-3: Explicit, implicit, and unsupported datatype conversions

From

To

tinyint

smallint

int

decimal

numeric

real

float

char, nchar

varchar, nvarchar

text

smallmoney

money

bit

smalldatetime

datetime

binary

varbinary

image

tinyint

-

I

I

I

I

I

I

E

E

U

I

I

I

U

U

I

I

U

smallint

I

-

I

I

I

I

I

E

E

U

I

I

I

U

U

I

I

U

int

I

I

-

I

I

I

I

E

E

U

I

I

I

U

U

I

I

U

decimal

I

I

I

I/E

I/E

I

I

E

E

U

I

I

I

U

U

I

I

U

numeric

I

I

I

I/E

I/E

I

I

E

E

U

I

I

I

U

U

I

I

U

real

I

I

I

I

I

-

I

E

E

U

I

I

I

U

U

I

I

U

float

I

I

I

I

I

I

-

E

E

U

I

I

I

U

U

I

I

U

char, nchar

E

E

E

E

E

E

E

I

I

E

E

E

E

I

I

I

I

E

varchar, nvarchar

E

E

E

E

E

E

E

I

I

E

E

E

E

I

I

I

I

E

text

U

U

U

U

U

U

U

E

E

U

U

U

U

U

U

U

U

U

smallmoney

I

I

I

I

I

I

I

I

I

U

-

I

I

U

U

I

I

U

money

I

I

I

I

I

I

I

I

I

U

I

-

I

U

U

I

I

U

bit

I

I

I

I

I

I

I

I

I

U

I

I

-

U

U

I

I

U

smalldatetime

U

U

U

U

U

U

U

E

E

U

U

U

U

-

I

I

I

U

datetime

U

U

U

U

U

U

U

E

E

U

U

U

U

I

-

I

I

U

binary

I

I

I

I

I

I

I

I

I

U

I

I

I

I

I

-

I

E

varbinary

I

I

I

I

I

I

I

I

I

U

I

I

I

I

I

I

-

E

image

U

U

U

U

U

U

U

U

U

U

U

U

U

U

U

E

E

U

Key: E     Explicit datatype conversion is required. I     Conversion can be done either implicitly or with an explicit datatype conversion function. I/E  Explicit datatype conversion function required when there is loss of precision or scale and arithabort numeric_truncation is on; otherwise, implicit conversion is allowed. U    Unsupported conversion. -    Conversion of a datatype to itself. These conversions are allowed but are meaningless.

Converting Character Data to a Non-Character Type

Character data can be converted to a non-character typežsuch as a money, date/time, exact numeric, or approximate numeric typežif it consists entirely of characters that are valid for the new type. Leading blanks are ignored. However, if a char expression that consists of a blank or blanks is converted to a datetime expression, SQL Server converts the blanks into the default datetime value of "Jan 1, 1900".

Syntax errors are generated when the data includes unacceptable characters. Following are some examples of characters that cause syntax errors:

• Commas or decimal points in integer data
• Commas in monetary data
• Letters in exact or approximate numeric data or bit stream data
• Misspelled month names in date/time data

Converting from One Character Type to Another

When converting from a multibyte character set to a single-byte character set, characters with no single-byte equivalent are converted to blanks.

text columns can be explicitly converted to char, nchar, varchar, or nvarchar. You are limited to the maximum length of the character datatypes, 255 bytes. If you do not specify the length, the converted value has a default length of 30 bytes.

Converting Numbers to a Character Type

Exact and approximate numeric data can be converted to a character type. If the new type is too short to accommodate the entire string, an insufficient space error is generated. For example, the following conversion tries to store a 5-character string in a 1-character type:

select convert(char(1), 12.34)
Insufficient result space for explicit conversion
of NUMERIC value '12.34' to a CHAR field.

Note: , When converting float data to a character type, the new type should be at least 25 characters long.

Rounding During Conversion to and from Money Types

The money and smallmoney types store 4 digits to the right of the decimal point, but round up to the nearest hundredth (.01) for display purposes. When data is converted to a money type, it is rounded up to four places.

Data converted from a money type follows the same rounding behavior if possible. If the new type is an exact numeric with less than three decimal places, the data is rounded to the scale of the new type. For example, when $4.50 is converted to an integer, it yields 5:

select convert(int, $4.50)            

 ----------- 
           5 

Data converted to money or smallmoney is assumed to be in full currency units such as dollars rather than in fractional units such as cents. For example, the integer value of 5 is converted to the money equivalent of 5 dollars, not 5 cents, in the us_english language.

Converting Date/time Information

Data that is recognizable as a date can be converted to datetime or smalldatetime. Incorrect month names lead to syntax errors. Dates that fall outside the acceptable range for the datatype lead to arithmetic overflow errors.

When datetime values are converted to smalldatetime, they are rounded to the nearest minute.

Converting Between Numeric Types

Data can be converted from one numeric type to another. If the new type is an exact numeric whose precision or scale is not sufficient to hold the data, errors can occur.

For example, if you provide a float or numeric value as an argument to a built-in function that expects an integer, the value of the float or numeric is truncated. However, Adaptive Server does not implicitly convert numerics that have a fractional part but returns a scale error message. For example, Adaptive Server returns error 241 for numerics that have a fractional part and error 257 if other datatypes are passed.

Use the arithabort and arithignore options to determine how Adaptive Server handles errors resulting from numeric conversions.

Note: , The arithabort and arithignore options have been redefined for release 10.0 or later. If you use these options in your applications, examine them to be sure they are still producing the desired behavior.

Arithmetic Overflow and Divide-by-Zero Errors

Divide-by-zero errors occur when Adaptive Server tries to divide a numeric value by zero. Arithmetic overflow errors occur when the new type has too few decimal places to accommodate the results. This happens during:

• Explicit or implicit conversions to exact types with a lower precision or scale
• Explicit or implicit conversions of data that falls outside the acceptable range for a money or date/time type
• Conversions of hexadecimal strings requiring more than 4 bytes of storage using hextoint

Both arithmetic overflow and divide-by-zero errors are considered serious, whether they occur during an implicit or explicit conversion. Use the arithabort arith_overflow option to determine how Adaptive Server handles these errors. The default setting, arithabort arith_overflow on, rolls back the entire transaction in which the error occurs. If the error occurs in a batch that does not contain a transaction, arithabort arith_overflow on does not roll back earlier commands in the batch, and Adaptive Server does not execute statements that follow the error-generating statement in the batch. If you set arithabort arith_overflow off, Adaptive Server aborts the statement that causes the error, but continues to process other statements in the transaction or batch.You can use the @@error global variable to check statement results.

Use the arithignore arith_overflow option to determine whether Adaptive Server displays a message after these errors. The default setting, off, displays a warning message when a divide-by-zero error or a loss of precision occurs. Setting arithignore arith_overflow on suppresses warning messages after these errors. The optional arith_overflow keyword can be omitted without any effect.

Scale Errors

When an explicit conversion results in a loss of scale, the results are truncated without warning. For example, when you explicitly convert a float, numeric, or decimal type to an integer, Adaptive Server assumes you want the result to be an integer and truncates all numbers to the right of the decimal point.

During implicit conversions to numeric or decimal types, loss of scale generates a scale error. Use the arithabort numeric_truncation option to determine how serious such an error is considered. The default setting, arithabort numeric_truncation on, aborts the statement that causes the error, but continues to process other statements in the transaction or batch. If you set arithabort numeric_truncation off, Adaptive Server truncates the query results and continues processing.

Note: , For entry level SQL92 compliance, set:
- arithabort arith_overflow off
- arithabort numeric_truncation on
- arithignore off

Domain Errors

The convert() function generates a domain error when the function's argument falls outside the range over which the function is defined. This happens rarely.

Conversions Between Binary and Integer Types

The binary and varbinary types store hexadecimal-like data consisting of a "0x" prefix followed by a string of digits and letters.

These strings are interpreted differently by different platforms. For example, the string "0x0000100" represents 65536 on machines that consider byte 0 most significant and 256 on machines that consider byte 0 least significant.

Binary types can be converted to integer types either explicitly, using the convert function, or implicitly. If the data is too short for the new type, it is stripped of its "0x" prefix and zero-padded. If it is too long, it is truncated.

Both convert and the implicit datatype conversions evaluate binary data differently on different platforms. Because of this, results may vary from one platform to another. Use the hextoint function for platform-independent conversion of hexadecimal strings to integers, and the inttohex function for platform-independent conversion of integers to hexadecimal values.

Converting Between Binary and Numeric or Decimal Types

In binary and varbinary data strings, the first two digits after "0x" represent the binary type: "00" represents a positive number and "01" represents a negative number. When you convert a binary or varbinary type to numeric or decimal, be sure to specify the "00" or "01" values after the "0x" digit; otherwise, the conversion will fail.

For example, here is how to convert the following binary data to numeric:

select convert(numeric
(38, 18),0x000000000000000006b14bd1e6eea0000000000000000000000000000000)

----------
123.456000

This example converts the same numeric data back to binary:

select convert(binary,convert(numeric(38, 18), 123.456))

-------------------------------------------------------------- 
0x000000000000000006b14bd1e6eea0000000000000000000000000000000

Converting Image Columns to Binary Types

You can use the convert function to convert an image column to binary or varbinary. You are limited to the maximum length of the binary datatypes, 255 bytes. If you do not specify the length, the converted value has a default length of 30 characters.

Converting Other Types to bit

Exact and approximate numeric types can be converted to the bit type implicitly. Character types require an explicit convert function.

The expression being converted must consist only of digits, a decimal point, a currency symbol, and a plus or minus sign. The presence of other characters generates syntax errors.

The bit equivalent of 0 is 0. The bit equivalent of any other number is 1.