Using CAST to Convert Datatypes
Normally, only similar datatypes can be compared in search conditions, but you can work around this by using CAST. Use the CAST clause in search conditions to translate one datatype into another. The syntax for the CAST clause is:
CAST (<value> | NULL AS datatype)
For example, the following WHERE clause uses CAST to translate a CHAR datatype, INTERVIEW_DATE, to a DATE datatype. This conversion lets you compare INTERVIEW_DATE to another DATE column, hire_date:
. . . WHERE hire_date = CAST(interview_date AS DATE)
You can use CAST to compare columns in the same table or across tables. CAST allows the conversions listed in the following table:
|
From datatype |
To datatype |
|---|---|
| NUMERIC |
CHARACTER, DATE |
|
CHARACTER |
NUMERIC, DATE |
|
DATE |
CHARACTER, NUMERIC |
Table 7: Compatible Datatypes for CAST