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 cout_big function

count_big, an aggregate function, finds the number of non-null values in a column. It returns the number of (distinct) non-null values or the number of selected rows as a bigint.

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

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.

The hextobigint function

Use the hextobigint function for platform-independent conversions of hexadecimal data to 64-bit integers. hextobigint accepts a valid hexadecimal string, with or without the “0x” prefix, enclosed in quotes, or the name of a character-type column or variable.

hextobigint returns the 64-bit integer equivalent of the hexadecimal string. The function always returns the same 64-bit integer equivalent for a given hexadecimal string, regardless of the platform on which it is executed.

The biginttohex function

Use the biginttohex function for platform-independent conversions of 64-bit integers to hexadecimal strings. biginttohex accepts any expression that evaluates to a 64-bit 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, or the page size of the server. 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).

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.

Table 16-14: Converting date formats with the style parameter. For key, see below

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

13

113

yy/dd/mm

14

114

mm/yy/dd

15

115

dd/yy/mm

-

16 or 116

mon dd yyyy HH:mm:ss

17

117

hh:mmAM

18

118

HH:mm

19

hh:mm:ss:zzzAM

20

HH:mm:ss:zzz

21

yy/mm/dd HH:mm:ss

22

yy/mm/dd hh:mm AM (or PM)

23

yyyy-mm-ddTHH:mm:ss

Key to table: “mon” indicates a month spelled out, “mm” the month number or minutes. “HH ”indicates a 24-hour clock value, “hh” a 12-hour clock value. The last row, 23, includes a literal “T” to separate the date and time portions of the format.

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.

When converting date data to a character type, use style numbers 1 through 7 (101 through 107) or 10 through 12 (110 through 112) in Table 16-14 to specify the display format. The default value is 100 (mon dd yyyy hh:miAM (or PM )). If date data is converted to a style that contains a time portion, that time portion reflects the default value of zero.When converting time data to a character type, use style number 8 or 9 (108 or 109) to specify the display format. The default is 100 (mon dd yyyy hh:miAM (or PM)). If time data is converted to a style that contains a date portion, the default date of Jan 1, 1900 displays.

Noteconvert with NULL in the style argument returns the same result as convert with no style argument. For example:

select convert(datetime, "01/01/01")
------------
Jan 1 2001 12:00AM
select convert(datetime, "01/01/01", NULL)
------------
Jan 1 2001 12:00AM