Transact-SQL User's Guide
|Chapter 10 Using the Built-In Functions in Queries|
|Datatype conversion functions|
Datatype conversions change an expression from one datatype to another and reformat date and time information. Adaptive Server provides three datatype conversion functions, convert, inttohex, and hextoint.
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.
Figure 10-1summarizes the datatype conversions supported by Adaptive Server.Figure 10-1: Explicit, implicit, and unsupported conversions
The general conversion function, convert, converts between a variety of datatypes and specifies a new display format for date and time information. Its syntax is:
convert(datatype, expression [, style] )
Here is an example that uses convert in the select list:
select title, convert(char(5), total_sales) from titles where type = "trad_cook"
title ------------------------------------ ----- Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 375 Fifty Years in Buckingham Palace Kitchens 15096 Sushi, Anyone? 4095 (3 rows affected)
In the following example, the total_sales column, an int column, is converted to a char(5) column so that it can be used with the like keyword:
select title, total_sales from titles where convert(char(5), total_sales) like "15%" and type = "trad_cook"
title --------------------------------- ----- Fifty Years in Buckingham Palace Kitchens 15096 (1 row affected)
Certain datatypes expect either a length or a precision and scale. If you do not specify a length, Adaptive Server uses the default length of 30 for character and binary data. If you do not specify a precision or scale, Adaptive Server uses the defaults of 18 and 0, respectively.
The following sections describe the rules Adaptive Server observes when converting different types of information.Converting character data to a noncharacter type
Character data can be converted to a noncharacter type--such as a money, date and 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, Adaptive Server converts the blanks into the Sybase default datetime value of "Jan 1, 1900".
Adaptive Server generates syntax errors if the data includes unacceptable characters. The following types of characters 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 and time data
When you convert 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, unichar, varchar, univarchar, or nvarchar. You are limited to the maximum length of the character datatypes, the page size. If you do not specify the length, the converted value has a default length of 30 bytes.Converting numbers to a character type
You can convert exact and approximate numeric data 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 five-character string in a one-character type:
select convert(char(1), 12.34)
It fails because the char datatype is limited to one character, and the numeric 12.34 requires five characters for the conversion to be successful.Rounding during conversion to or from money types
The money and smallmoney types store four 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 decimal 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 3 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 (1 row affected)
Adaptive Server assumes that data converted to money or smallmoney is in full currency units, such as dollars, rather than in fractional units, such as cents. For example, the integer value of five is converted to the money equivalent of five dollars, not five cents, in us_english.Converting date and 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 up 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. Use the arithabort and arithignore options to determine how these errors are handled.
The arithabort and arithignore options were redefined in SQL Server release 10.0. If you use these options in your applications, examine them to make sure they are still functioning correctly.
Adaptive Server binary and varbinary data is platform-specific; the type of hardware you are using determines how the data is stored and interpreted. Some platforms consider the first byte after the "0x" prefix to be the most significant; others consider the first byte to be the least significant.
The convert function treats Sybase binary data as a string of characters, rather than as numeric information. convert takes no account of byte order significance when converting a binary expression to an integer or an integer expression to a binary value. Because of this, conversion results can vary from one platform to another.
Before converting a binary string to an integer, convert strips it of its "0x" prefix. If the string consists of an odd number of digits, Adaptive Server inserts a leading zero. If the data is too long for the integer type, convert truncates it. If the data is too short, convert adds leading zeros to make it even, and then pads it with zeros on the right.
Suppose you want to convert the string 0x00000100 to an integer. On some platforms, this string represents the number 1; on others, the number 256. Depending on which platform executes the function, convert returns either 1 or 256.Converting hexadecimal data
For conversion results that are reliable across platforms, use the hextoint and inttohex functions.
hextoint accepts literals or variables consisting of digits and the uppercase and lowercase letters A-F, with or without a "0x" prefix. The following are all valid uses of hextoint:
select hextoint("0x00000100FFFFF") select hextoint("0x00000100") select hextoint("100")
hextoint strips it of the "0x" prefix. If the data exceeds 8 digits, hextoint truncates it. If the data is less than 8 digits, hextoint right-justifies and pads it with zeros. Then hextoint returns the platform-independent integer equivalent. The above expressions all return the same value, 256, regardless of the platform that executes the hextoint function.
The inttohex function accepts integer data and returns an 8-character hexadecimal string without a "0x" prefix. inttohex always returns the same results, regardless of which platform you are using.Converting image data to binary or varbinary
Use the convert function to convert an image column to binary or varbinary. You are limited to the maximum length of the binary datatypes, which is the page size of the server. If you do not specify the length, the converted value has a default length of 30 characters.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)
This example converts the same numeric data back to binary:
select convert(binary,convert(numeric(38, 18), 123.456))
This sections describes the types of errors that can occur during datatype conversions.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 datetime type
Conversions of strings longer than 4 bytes using hextoint
Both arithmetic overflow and divide-by-zero errors are considered serious, whether they occur during implicit or explicit conversions. 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 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 really 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.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 should happen very rarely.
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 65,536 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, with the convert function, or implicitly. The data is stripped of its "0x" prefix and then zero-padded if it is too short for the new type or truncated if it is too long.
Both convert and the implicit datatype conversions evaluate binary data differently on different platforms. Therefore, the 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.
Use the hextoint function for platform-independent conversions of hexadecimal data to integers. hextoint accepts a valid hexadecimal string, with or without the "0x" prefix, enclosed in quotes, or the name of a character-type column or variable.
hextoint returns the integer equivalent of the hexadecimal string. The function always returns the same integer equivalent for a given hexadecimal string, regardless of the platform on which it is executed.
Use the inttohex function for platform-independent conversions of integers to hexadecimal strings. inttohex accepts any expression that evaluates to an integer. It always returns the same hexadecimal equivalent for a given expression, regardless of the platform on which it is executed.
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, or the page size of the server. If you do not specify the length, the converted value has a default length of 30 characters.
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.
The style parameter of convert provides a variety of date display formats for converting datetime or smalldatetime data to char or varchar. The number argument you supply as the style parameter determines how the data is displayed. The year can be displayed in either 2 digits or 4 digits. Add 100 to a style value to get a 4-digit year, including the century (yyyy).
Table 10-14 shows the possible values for style and the variety of date formats you can use. When you use style with smalldatetime, the styles that include seconds or milliseconds will show zeros in those positions.
Without century (yy)
With century (yyyy)
0 or 100
mon dd yyyy hh:mm AM (or PM)
dd mon yy
mon dd, yy
9 or 109
Default + milliseconds
mon dd yyyy hh:mm:sss AM (or PM)
The default values, style 0 or 100, and 9 or 109, always return the century (yyyy).
Here is an example of the use of convert's style parameter:
select convert(char(12), getdate(), 3)
This converts the current date to style 3, dd/mm/yy.