CAST Data-Type Conversions in SQL

 

May be you have  covered the data types that SQL recognizes and supports. Ideally, each column in a
database table has a perfect choice of data type. In this non-ideal world, however, exactly what that
perfect choice may be isn't always clear. In defining a database table, suppose you assign a data type
to a column that works perfectly for your current application. Later, you want to expand your
application's scope or write an entirely new application that uses the data differently. This new use
could require a data type different from the one you originally chose.

You may want to compare a column of one type in one table with a column of a different type in a
different table. For example, you could have dates stored columns contain the same things (dates, for
example), the fact that the types are different may prevent you from making the comparison. In SQL-86
and SQL-89, type incompatibility posed a big problem. SQL-92, however, introduced an easy-to-use
solution in the CAST expression. The CAST expression converts table data or host variables of one type
to another type. After you make the conversion, you can proceed with the operation or analysis that you
originally envisioned.

Naturally, you face some restrictions when using the CAST expression. You can't just indiscriminately

convert data of any type into any other type. The data that you're converting must be compatible with
the new data type. You can, for example, use CAST to convert the CHAR(10) character string '1998-04-26'
to the DATE type. But you can't use CAST to convert the CHAR(10) character string 'rhinoceros' to the
DATE type. You can't convert an INTEGER to the SMALLINT type if the former exceeds the maximum

size of a SMALLINT.

You can convert an item of any character type to any other type (such as numeric or date) provided that
the item's value has the form of a literal of the new type. Conversely, you can convert an item of any
type to any of the character types, provided that the value of the item has the form of a literal of the

original type.

The following list describes some additional conversions you can make:

� Any numeric type to any other numeric type. If converting to a type of less fractional precision, the
    system rounds or truncates the result.

� Any exact numeric type to a single component interval, such as INTERVAL DAY or INTERVAL SECOND.

� Any DATE to a TIMESTAMP. The time part of the TIMESTAMP fills in with zeros.

� Any TIME to a TIME with a different fractional-seconds precision or a TIMESTAMP. The date part of the
    TIMESTAMP fills in with the current date.

� Any TIMESTAMP to a DATE, a TIME, or a TIMESTAMP with a different fractional-seconds precision.

� Any year-month INTERVAL to an exact numeric type or another yearmonth INTERVAL with different
    leading-field precision.

� Any day-time INTERVAL to an exact numeric type or another day-time INTERVAL with different leading-
    field precision.

Using CAST within SQL

Suppose that you work for a sales company that keeps track of prospective employees as well as
employees whom you've actually hired. You list the prospective employees in a table named PROSPECT,
and you distinguish them by their Social Security numbers, which you store as a CHAR(9) type. You list

the employees in a table named EMPLOYEE, and you distinguish them by their Social Security numbers,
which are of the INTEGER type. You now want to generate a list of all people who appear in both tables.
You can use CAST to perform the task, as follows:

SELECT * FROM EMPLOYEE

WHERE EMPLOYEE.SSN =

CAST(PROSPECT.SSN AS INTEGER) ;

Using CAST between SQL and the host language
The key use of CAST is to deal with data types that are in SQL but not in the host language that you use.
The following list offers some examples of these data types:

� SQL has DECIMAL and NUMERIC, but FORTRAN and Pascal don't.

� SQL has FLOAT and REAL, but standard COBOL doesn't.

� SQL has DATETIME, which no other language has.

Suppose that you want to use FORTRAN or Pascal to access tables with DECIMAL(5,3) columns, and
you don't want the inaccuracies that result from converting those values to the REAL data type of
FORTRAN and Pascal. You can perform this task by CASTing the data to and from characterstring

host variables. You retrieve a numeric salary of 198.37 as a CHAR(10) value of '0000198.37'. Then if
you want to update that salary to 203.74, you can place that value in a CHAR(10) as '0000203.74'.
First, you use CAST to change the SQL DECIMAL(5,3) data type to the CHAR(10) type for the employee
whose ID number you're storing in the host variable :emp_id_var, as follows:

SELECT CAST(Salary AS CHAR(10)) INTO :salary_var

FROM EMP

WHERE EmpID = :emp_id_var ;

Then the application examines the resulting character string value in :salary_var, possibly sets the string
to a new value of '000203.74', and then updates the database by using the following SQL code:

UPDATE EMP

SET Salary = CAST(:salary_var AS DECIMAL(5,3))

WHERE EmpID = :emp_id_var ;

Dealing with character-string values like '000198.37' is awkward in FORTRAN or Pascal, but you can write
a set of subroutines to do the necessary manipulations. You can then retrieve and update any SQL data
from any host language and get and set exact values.

The general idea is that CAST is most valuable for converting between host types and the database rather
than for converting within the database.

 

an Simple example of conversion is as:

use NIR

declare @as int;

select @as= dbo.emp.Salary from dbo.emp where dbo.emp.Id=1;

 

declare @ds varchar(22);

set @ds= CAST(@as as varchar(22));

print @ds;