SQL CAST() Function

All of the aforementioned functions relate to specific ways to manipulate character, date/time, or numeric datatypes. But you may need to convert data from one datatype to another or convert NULL values to something meaningful.

The CAST function allows you to convert data from one datatype to another. The general format of the function is:

CAST (Expression AS DataType) 

The CAST function is actually unnecessary in many situations. Let’s take the situation where you want to execute this statement, where the Quantity column is defined as a character column:

SELECT 2 * Quantity FROM table; 

You might think that the statement would fail due to the fact that Quantity is not defined as a numeric column. However, most SQL databases are smart enough to automatically convert the Quantity column to a numeric value so it can be multiplied by 2.

Here’s an example where you may need to use the CAST function. Let’s say that you have a column with dates stored in a character column. You would like to convert those dates to a true date/time column. This statement illustrates how the CAST function can handle that conversion:

SELECT'2009-04-11' AS 'Original Date', CAST ('2009-04-11' AS DATETIME) AS 'Converted Date'FROM DUAL; 

The output is:

Original Date         Converted Date

2009-04-11          2009-04-11 00:00:00

The Original Date column looks like a date, but it is really just character data. In contrast, the Converted Date column is a true date/time column, as evidenced by the time value, which is now shown.

The equivalent statement for the previous CAST function in Oracle is:

SELECT'2009-04-11' AS "Original Date", CAST ('2009-04-11' AS DATETIME) AS "Converted Date"FROM DUAL;