Datatype conversion guidelines and constraints

You can convert character data to a noncharacter 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, Adaptive Server converts the blanks into the default datetime value of “Jan 1, 1900.”

Syntax errors occur when the data includes unacceptable characters. These are 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 and time data

Implicit conversions between unichar/univarchar and datetime/smalldatetime are supported.

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

You can explicitly convert text and unitext columns to char, nchar, varchar, unichar, univarchar, or nvarchar. You are limited to the maximum length of the character 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 bytes.

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

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

You can implicitly convert to unitext from other character and binary datatypes. You can explicitly convert from unitext to other datatypes, and vice versa. However, the conversion result is limited to the maximum length of the destination datatype. When a unitext value cannot fit the destination buffer on a Unicode character boundary, data is truncated. If you have set enable surrogate processing, the unitext value is never truncated in the middle of a surrogate pair of values, which means that fewer bytes may be returned after the datatype conversion. For example, if a unitext column ut in table tb stores the string “U+0041U+0042U+00c2” (U+0041 represents the Unicode character “A”), this query returns the value “AB” if the server’s character set is UTF-8, because U+00C2 is converted to 2-byte UTF-8 0xc382:

select convert(char(3), ut) from tb

Currently, the alter table modify command does not support text, image, or unitext columns as the modified column. To migrate from a text to a unitext column, you must first use bcp out to copy the existing data out, create a table with unitext columns, and then use bcp in to place data into the new table. This migration path works only when you invoke bcp with the -Jutf8 option.

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

Data that is recognizable as a date can be converted to datetime, smalldatetime, date, or time. 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.

You can convert data from one numeric type to another. Errors can occur if the new type is an exact numeric with precision or scale that is insufficient to hold the data.

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

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 (little-endian) and 256 on machines that consider byte 0 least significant (big-endian).

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. Use the hextobigint function for platform-independent conversion of hexadecimal strings to 64-bit integers, and the biginttohex function for platform-independent conversion of 64-bit integers to hexadecimal values.

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

For example, to convert the following binary data to numeric use:

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

----------
123.456000

To convert the same numeric data back to binary use:

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

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

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

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.

For conversion results that are reliable across platforms, use the hextoint and inttohex functions.

Similar functions, hextobigint and biginttohex, are available to convert to and from 64-bit integers.

hextoint accepts literals or variables consisting of digits and the uppercase and lowercase letters A – F, with or without a “0x” prefix. These are all valid uses of hextoint:

select hextoint("0x00000100FFFFF")
select hextoint("0x00000100")
select hextoint("100")

hextoint strips data of the “0x” prefix. If the data exceeds 8 digits, hextoint truncates it. If the data is fewer 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 platform.

Implicit and explicit conversions are allowed where a decreased precision results in the loss of data.

Implicit conversion between types without matching primary fields may cause either data truncation, the insertion of a default value, or an error message to be raised. For example, when a bigdatetime value is converted to a date value, the time portion is truncated leaving only the date portion. If a bigtime value is converted to a bigdatetime value, a default date portion of Jan 1, 0001 is added to the new bigdatetime value. If a date value is converted to a bigdatetime value, a default time portion of 00:00:00.000000 is added to the bigdatetime value.

You can use the convert function to change NULL to NOT NULL and NOT NULL to NULL.