Oracle CAST() Function

The Oracle (PL/SQL) CAST() function converts an expression from one datatype to another datatype. If the conversion fails, the function will return an error. Otherwise, it will return the converted value.

Note: Use the TRY_CAST() function to return a NULL (instead of an error) if the conversion fails.

Syntax

CAST(expression AS data_type [ ( length ) ])

Parameters

`expression`	`Required.` Specify the value to convert to another datatype.
`data_type`	`Required.` Specify the datatype to convert to. See the data types of Oracle. Note that the Alias data types cannot be used.
`length`	`Optional.` Specify the length of the resulting data type, for data types that allow a user specified length. Default is 30.

Return Value

Returns the expression converted to another data_type.

Convert to INT

The CAST() function can be used to convert a value to a INT type. For example - in the example below, 123.456 is converted to INT datatype.

SELECT CAST(123.456 AS INT);
Result: 123

Convert to DECIMAL

The CAST() function can be used to convert a value to a DECIMAL type. For example - in the example below, '123.456' is converted to DECIMAL datatype.

SELECT CAST('123.456' AS DECIMAL(5, 2));
Result: 123.46

Convert to CHAR

The CAST() function can be used to convert a value to a CHAR type. For example - in the example below, 123 is converted to CHAR datatype.

SELECT CAST(123 AS CHAR(4));
Result: '123'

Convert to NCHAR

The CAST() function can be used to convert a value to a NCHAR type. For example - in the example below, 123 is converted to NCHAR datatype.

SELECT CAST(123 AS NCHAR(10));
Result: '123'

Convert to VARCHAR

The CAST() function can be used to convert a value to a VARCHAR type. For example - in the example below, 123 is converted to VARCHAR datatype.

SELECT CAST(123 AS VARCHAR);
Result: '123'

Convert to DATE

The CAST() function can be used to convert a value to a DATE type. For example - in the example below, '2018-08-18' is converted to DATE datatype.

SELECT CAST('2018-08-18' AS DATE);
Result: '2018-08-31'

Convert to TIME

The CAST() function can be used to convert a value to a TIME type. For example - in the example below, '10:38:42' is converted to TIME datatype.

SELECT CAST('10:38:42' AS TIME);
Result: '10:38:42'

Convert to DATETIME

The CAST() function can be used to convert a value to a DATETIME type. For example - in the example below, '2018-08-18 10:38:42' is converted to DATETIME datatype.

SELECT CAST('2018-08-18 10:38:42' AS DATETIME);
Result: '2018-08-18 10:38:42'

❮ Oracle Functions