How to Use CAST Data-Type Conversions with SQL
Ideally, each column in a SQL 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.
Suppose that later on 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 as character data in one table and as date data in another table.
Even if both columns contain the same sort of information (dates, for example), the fact that the types are different may prevent you from making the comparison. In the earliest SQL standards, 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 ‘2007-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 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 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 less fractionally precise type, 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 year-month 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.
How to use CAST within SQL
Suppose you work for a company that keeps track of prospective employees as well as the employees 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 happen to 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:
SELECT * FROM EMPLOYEE WHERE EMPLOYEE.SSN = CAST(PROSPECT.SSN AS INTEGER) ;
How to use CAST between SQL and the host language
The key use of CAST is to deal with data types that are available 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 you want to use FORTRAN or Pascal to access tables with DECIMAL(5,3) columns, and you don’t want any inaccuracies to result from converting those values to the REAL data type used by FORTRAN and Pascal. You can perform this task by using CAST to move the data to and from character-string 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 ;
The FORTRAN or Pascal 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 calling 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 such as ‘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.