Data type conversion and CAST function

Data type conversion and CAST function

Implicit data type conversions are possible in SQL implementations. For example, if a smallint is compared to an int in T-SQL, the smallint is implicitly converted to int before the comparison proceeds. See BOL (SQL Server Books Online - electronic documentation and tutorials) for details about implicit and explicit conversions in MS SQL Server.

Example 5.9.1

Find the average price of laptops with the prefix text “Average price = “.

If you run the query

SELECT 'Average price = ' + AVG(price)
FROM Laptop;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]
the following error message will be obtained:

Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.

This message implies that the system cannot accomplish implicit conversion of the character constant “Average price = " to data type money of the price column. In such cases, explicit type conversion can help. In so doing, as the above message says, you can use the CONVERT function. However this function is not based on the SQL-92 standard. Because of this, it is preferable to use the standard function CAST. So, we start with CAST.

If we rewrite above query as follows

SELECT 'Average price = ' + CAST(AVG(price) AS CHAR(15))
FROM Laptop;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

we get desired result:

Average price = 1003.33

We have used the expression for explicit type conversion CAST to bring the average value to the character view. The CAST statement has very simple syntax:

CAST(< expression > AS < data type >)

Important

Firstly, it should be noted that some data type conversions are not supported. (SQL-92 Standard involves the table of allowed conversions). Secondly, NULL value is converted into NULL value also.

Let us consider another example.

Example 5.9.2

Define the average launching year from the Ships table.

The query

SELECT AVG(launched)
FROM Ships;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]
gives 1926. In principle, it is correct, because a year is integer number. However arithmetic mean will be about 1926,9091. It should be noted that aggregate functions (except COUNT which always returns integer value) inherits the type of data to be processed. Because the launched field is integer-valued, we have gotten the average value without fractional part (not rounded off).

What must we do if the result ought to be obtained with two digits after decimal point? As mentioned above, applying the CAST statement to the average value gives no result. Indeed,

SELECT CAST(AVG(launched) AS NUMERIC(6,2))
FROM Ships;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]
returns the value of 1926.00. Consequently, the CAST statement should be applied to the argument of the aggregate function:

SELECT AVG(CAST(launched AS NUMERIC(6,2)))
FROM Ships;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

The result - 1926.90909- is not exactly correct. This is because of implicit conversion that was accomplished when calculating the average value. Another step:

SELECT CAST(AVG(CAST(launched AS NUMERIC(6,2))) AS NUMERIC(6,2))
FROM Ships;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

It is the correct result - 1926.91. However this solution looks too cumbersome. Let implicit conversion to work for us:

SELECT CAST(AVG(launched*1.0) AS NUMERIC(6,2))
FROM Ships;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

Thus, we use implicit conversion of the argument from integer to exact numeric type by multiplying it by real unity. After that, explicit conversion is applied to the result of the aggregate function.

The same conversions can be made with aid of the CONVERT function:

SELECT CONVERT(NUMERIC(6,2), AVG(launched*1.0))
FROM Ships;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

The CONVERT function has the following syntax:

CONVERT (< data type >[(< length >)], < expression > [, < style >])

The main distinction of the CONVERT function from the CAST statement is that the first allows formatting data (for example, temporal data of datetime type) when converting them to character data and specifying the format when converting character data to datetime. The values of integer optional argument style correspond to different formats. Let us consider the following example:

SELECT CONVERT(char(25), CONVERT(datetime,'20030722'));
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

Here, the string representation of a date is converted to datetime following the reverse conversion to demonstrate the result of formatting. Since the style argument is omitted, default value is used (0 or 100). As a result, we obtain

Jul 22 2003 12:00AM

Below are some values of the style argument and corresponding results from the above example. Note, the style values greater than 100 give four-place year.

107/22/03
1103/07/22
322/07/03
1212003-07-22 00:00:00.000

All possible values of the style argument are given in BOL.

Suggested exercises: 32, 35, 53, 54, 58, 69, 78, 8191115, 119

There is one feature of use of operator CAST in SQL Server, which is connected with conversion of a number to its character representation. What will occur, if the quantity of symbols in a number exceeds the size of a string? For example,

SELECT CAST(1234.6 AS VARCHAR(5))
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

We would expect that we’ll receive the error message. Correctly, here is this message:

Arithmetic overflow error converting numeric to data type varchar.

It is natural that we shall expect the same message when running the following operator:

SELECT CAST(123456 AS VARCHAR(5))
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

But no. As a result we shall receive a symbol «*» instead of the error message. We do not undertake to judge with what it is connected, however, one day we have faced a problem of diagnostics of a bug in a code in which return transformation to numerical type was carried out afterwards.

In our elementary example it will look like:

SELECT CAST(CAST(123456 AS VARCHAR(5)) AS INT)
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]

Just here we receive the error:

Syntax error converting the varchar value '*' to a column of data type int.

Note

The CONVERT function in Transact-SQL behaves similarly.

Conversion of MONEY data type

The monetary data type is not standard. SQL Server has two monetary types:

money: range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807

smallmoney: range from -214 748,3648 to 214 748,3647

Scale 4 is for both types.

A constant of money data type can be set by means of a prefix $, or by using the transformation of types, for example:

select 1.2 num, $1.2 mn1, cast(1.2 as MONEY) mn2;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]
nummn1mn2
1.21,201,20

Pay attention to a comma as a separator of “dollars” and “cents” - not a dot!

Transformation to the integer data type for numbers and money is not the same: in the first case the fractional part is rejected, in the second, rounding takes place.

select cast(1.75 as INT) int_num, cast($1.75 as INT) int_mon;
mssql
🚫
[[ error ]]
[[ column ]]
[[ value ]]
int_numint_mon
12

It is money, it is impossible to lose them so easily!