loading..
Русский    English
22:23

Data type conversion and CAST function page 2

Let us consider another example. 

Example 5.9.2

Define the average launching year from the Ships table.

The query

Console
Execute
  1. SELECT AVG(launched)
  2. FROM Ships;
gives 1926. In principle, it is correct, because a year is integer number. However arithmetic mean will be about 1926,2381. 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,

Console
Execute
  1. SELECT CAST(AVG(launched) AS NUMERIC(6,2))
  2. FROM Ships;
returns the value of 1926.00. Consequently, the CAST statement should be applied to the argument of the aggregate function:

Console
Execute
  1. SELECT AVG(CAST(launched AS NUMERIC(6,2)))
  2. FROM Ships;

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

Console
Execute
  1. SELECT CAST(AVG(CAST(launched AS NUMERIC(6,2))) AS NUMERIC(6,2))
  2. FROM Ships;

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

Console
Execute
  1. SELECT CAST(AVG(launched*1.0) AS NUMERIC(6,2))
  2. FROM Ships;

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:

Console
Execute
  1. SELECT CONVERT(NUMERIC(6,2), AVG(launched*1.0))
  2. FROM Ships;

The CONVERT function has the following syntax:

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

Console
Execute
  1. SELECT CONVERT(char(25), CONVERT(datetime,'20030722'));

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.

1 07/22/03
11 03/07/22
3 22/07/03
121 2003-07-22 00:00:00.000

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

Next page

Suggested exercises: 32, 35, 53, 54, 58, 69, 78, 81, 91, 115, 119

Bookmark and Share
Pages 1 2 3
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100