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) |
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 |
hh:miAM(or PM) |
|
14 |
114 |
hh:mi |
|
14 |
114 |
hh:mi:ss:mmmAM(or PM) |
|
15 |
115 |
hh:mi:ss:mmm |
|
N/A |
16 or 116 |
mon dd yyy hh:mi:ss |
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 11-13 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 will reflect 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 will be displayed.
convert 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
