Date Functions Security Functions

Transact-SQL User's Guide

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.

Supported Conversions

Figure 10-1 summarizes the datatype conversions supported by Adaptive Server:

Using the General Purpose Conversion Function: convert

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.

Conversion Rules

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:

Converting from One Character Type to Another

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

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)

It fails because the char datatype is limited to 1 character, and the numeric 12.34 requires 5 characters for the conversion to be successful.

Rounding During Conversion to or 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 4 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 5 would be converted to the money equivalent of 5 dollars, not 5 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.

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

Converting Binary-Like Data

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:

hextoint("0x00000100FFFFF")
hextoint("0x00000100")
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, 255 bytes. 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)
----------------------
123.456000000000000000

This example converts the same numeric data back to binary:

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

0x000000000000000006b14bd1e6eea0000000000000000000000000000000

Conversion Errors

The following sections describe 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:

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.

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 65,536 on machines that consider byte 0 most significant and 256 on machines that consider byte 0 least significant.

The convert Function and Implicit Conversions

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.

The hextoint Function

Use the hextoint function for platform-independent conversions of hexadecimal data to integers. hextoint accepts a valid hexadecimal string, with or without a "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.

The inttohex Function

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.

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 Types

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.

Changing the Display Format for Dates

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

Following is a table of 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.

Table 10-13: Converting date formats with the style parameter

Without Century (yy)

With Century (yyyy)


Standard


Output

-

0 or 100

Default

mon dd yyyy hh:mm AM (or PM)

1

101

USA

mm/dd/yy

2

2

SQL standard

yy.mm.dd

3

103

English/French

dd/mm/yy

4

104

German

dd.mm.yy

5

105

dd-mm-yy

6

106

dd mon yy

7

107

mon dd, yy

8

108

hh:mm:ss

-

9 or 109

Default + milliseconds

mon dd yyyy hh:mm:sss AM (or PM)

10

110

USA

mm-dd-yy

11

111

Japan

yy/mm/dd

12

112

ISO

yymmdd

The default values, style 0 or 100, and 9 or 109, always return the century (yyyy).

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

Date Functions Security Functions