Encountering errors while converting between data types can be frustrating when working with SQL databases like DuckDB. But it usually means that something’s wrong. In most cases these errors occur because we’re trying to perform an impossible conversion, like from a number to a date or something.

But sometimes errors can get in the way, especially when we’re trying to convert a bunch of values. Sometimes it would be better for the system to return NULL for such failed conversions than to return an error and mess up the whole operation. Fortunately, we can do this.

About the try_cast() Function

The try_cast() function allows us to perform data conversions without being interrupted by errors. It works just like the cast() function, except that it returns NULL whenever a conversion fails instead of an error, like the cast() function does.

The syntax goes like this:

try_cast(expr AS type-name)

Where expr is the value we want to convert, and type-name is the new data type.

DuckDB isn’t the only DBMS that supports the try_cast() function. Some other DBMSs include such a function, such as with SQL Server’s implementation of try_cast().

Examples Of Successful Conversions

Here’s an example of using try_cast() to perform a successful data conversion (i.e. one that doesn’t fail):

SELECT try_cast(20250101 AS varchar);

Here’s what that might look like when output using duckbox mode in the DuckDB CLI:

+-------------------------------+
| TRY_CAST(20250101 AS VARCHAR) |
| 20250101                      |
|            varchar            |
+-------------------------------+
|           20250101            |
+-------------------------------+

When we use duckbox mode, it shows us the data type in the column header. But we can also use the typeof() function to get the data type:

SELECT 
    typeof(20250101) AS original,
    typeof(try_cast(20250101 AS varchar)) AS converted;

Output:

+----------+-----------+
| original | converted |
+----------+-----------+
| INTEGER  | VARCHAR   |
+----------+-----------+

Here it is again, but this time we’ll put the value into a variable:

SET VARIABLE num = 20250101;
SELECT 
    typeof(getvariable('num')) AS original,
    typeof(try_cast(getvariable('num') AS varchar)) AS converted;

Output:

+----------+-----------+
| original | converted |
+----------+-----------+
| INTEGER  | VARCHAR   |
+----------+-----------+

Examples With Failed Conversions

Given this article is about how try_cast() can handle failed conversions, let’s dive into some of those.

As mentioned, we can’t convert from a number to a date. Here’s what happens when we try to do that:

SELECT try_cast(20250101 AS date);

Result:

NULL

As expected, try_cast() returned NULL. This happened because the conversion failed.

Here’s what happens when we use cast() instead of try_cast():

SELECT cast(20250101 AS date);

Result:

Conversion Error:
Unimplemented type for cast (INTEGER -> DATE)

LINE 1: SELECT cast(20250101 AS date);
               ^

The cast() function produces an error.

Multiple Conversions in a Single Query

The try_cast() function can be especially useful when performing multiple conversions. If some of the conversions fail, we’ll still get the result of the successful ones. This contrasts with the cast() function where a single failure will result in an error, preventing any successful conversions from being returned.

Here’s an example of what I mean:

SELECT 
    value,
    try_cast(value AS integer) AS as_integer,
    try_cast(value AS double) AS as_double
FROM 
    (VALUES 
        ('123'),
        ('45.67'),
        ('abc'),
        (''),
        ('999'),
        ('3.14159'),
        ('NULL'),
        ('0')
    ) AS t(value);

Result:

+---------+------------+-----------+
|  value  | as_integer | as_double |
+---------+------------+-----------+
| 123     | 123        | 123.0     |
| 45.67   | 46         | 45.67     |
| abc     | NULL       | NULL      |
|         | NULL       | NULL      |
| 999     | 999        | 999.0     |
| 3.14159 | 3          | 3.14159   |
| NULL    | NULL       | NULL      |
| 0       | 0          | 0.0       |
+---------+------------+-----------+

Some of these conversions failed, but we still got results for the successful ones. Any failed conversions returned NULL for those ones, and the successful ones returned the converted value.

Here’s what happens if we change it to cast():

SELECT 
    value,
    cast(value AS integer) AS as_integer,
    cast(value AS double) AS as_double
FROM 
    (VALUES 
        ('123'),
        ('45.67'),
        ('abc'),
        (''),
        ('999'),
        ('3.14159'),
        ('NULL'),
        ('0')
    ) AS t(value);

Result:

Conversion Error:
Could not convert string 'abc' to INT32

LINE 3:     cast(value AS integer) AS as_integer,
            ^

We get nothing but an error. We can’t even see the successful conversions.