How to Do Type Casting

In SQL, type casting converts the data type of a value or expression to a different one. Here are some examples:

• CAST function

The CAST function converts a value from one data type to another.

Syntax:

CAST(expression AS data_type)

Here’s an example:

SELECT CAST('10.4123' AS INT);

Output: 10

• Explicit type conversion

You can explicitly convert a value from one data type to another by specifying the target data type before the value.

Syntax:

value::DATATYPE

Here are some examples:

SELECT 10.560688::integer;

Output: 11

SELECT '10:45'::time;

Output: 10:45:00

It’s important to make sure that the data type you are converting to can accommodate the range and precision of the value you are converting. SQL will return an error if the target data type is not large enough to hold the converted value. Also beware of automatic date parsing — it may be better to explicitly define the input date format:

select '2/7/2023'::date, to_date('2/7/2023','DD/MM/YYYY');

Output:

Note how the database assumes M/D/Y format unless specified otherwise.