When it comes to converting between data types, DuckDB performs implicit conversions when required, while also enabling us to perform explicit conversions. Implicit conversions are performed automatically by DuckDB when we do some other operation, such as use a function that requires its argument/s in a different data type than the one we’re providing. Regarding explicit conversions, we have the option of using a function like cast() or try_cast(), or using the shorthand method.

In this article we’ll take a quick look at how to convert between data types using the shorthand method.

Syntax

The syntax for the shorthand method of data conversions is quite simple. It goes like this:

expr::TYPENAME

Where expr is the expression/value that you want to convert, and TYPENAME is the name of the data type you want the value converted to.

Example

Here’s a quick example to demonstrate:

SELECT 192::VARCHAR;

If we run that using duckbox mode in the DuckDB CLI, we might see something like this:

+----------------------+
| CAST(192 AS VARCHAR) |
|       varchar        |
+----------------------+
|         192          |
+----------------------+

DuckDB’s duckbox mode helpfully presents the data type in the column header. In this case we can see that the number has been cast as a varchar as specified. Actually, the header also shows us the cast operation as if we’d used the cast() function. That’s the exact code we’d use if we were to use cast() to perform the conversion. Here they are alongside each other:

SELECT 
    cast(192 AS varchar) AS cast_function,
    192::VARCHAR AS shorthand;

Output:

+---------------+-----------+
| cast_function | shorthand |
|    varchar    |  varchar  |
+---------------+-----------+
|      192      |    192    |
+---------------+-----------+

However, if we’re not using duckbox mode in the DuckDB CLI, we can use the typeof() function to check the resulting data type:

SELECT typeof(192::VARCHAR);

Output:

VARCHAR

Truncation

In some cases the value may be truncated, depending on the value being converted. For example, if we convert a timestamp value to a date, then the time portion will be truncated.

Example:

SET VARIABLE theValue = timestamp '2029-01-01 18:30:00';
SELECT 
    getvariable('theValue') AS original,
    getvariable('theValue')::date AS converted;

Result:

+---------------------+------------+
|      original       | converted  |
+---------------------+------------+
| 2029-01-01 18:30:00 | 2029-01-01 |
+---------------------+------------+

Rounding

Rounding can also occur in some cases when performing conversions. For example, if we convert a decimal value to an integer, the fractional part will be removed, and the resulting integer may be rounded up or down, depending on the value of the fractional part. Basically, if the fractional part is non-zero then it will be rounded one way or another.

Example:

SET VARIABLE theValue = 21.50;
SELECT 
    getvariable('theValue') AS original,
    getvariable('theValue')::integer AS converted;

Result:

+----------+-----------+
| original | converted |
+----------+-----------+
| 21.50    | 22        |
+----------+-----------+

In this case, the value 21.50 was rounded up to 22.

Here’s what happens when the fractional part is lower than .50:

SET VARIABLE theValue = 21.49;
SELECT 
    getvariable('theValue') AS original,
    getvariable('theValue')::integer AS converted;

Result:

+----------+-----------+
| original | converted |
+----------+-----------+
| 21.49    | 21        |
+----------+-----------+

This time it was rounded down.

When a Value Can’t Be Converted

Some data types simply can’t be converted to another data type. For example we can’t convert a number to a date. And we can’t convert a string like “duckdb” to an integer. When we try to perform such conversions, DuckDB will usually throw an error.

Here’s an example of trying to convert a number to a date:

SELECT 20201010::date;

Output:

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

LINE 1: SELECT 20201010::date;
                       ^

And here’s one that fails when we try to convert a string to an integer:

SELECT 'duckdb'::integer;

Output:

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

LINE 1: SELECT 'duckdb'::integer;
                       ^

But not all strings will fail. If the string resembles a number, then DuckDB will work it out and go ahead with the conversion:

SELECT '123'::integer;

Output:

+------------------------+
| CAST('123' AS INTEGER) |
|         int32          |
+------------------------+
|          123           |
+------------------------+