Summary: in this tutorial, you will learn how to use the Db2 CAST
expression to convert a value of a type to another.
Db2 CAST
expression overview
The following query adds the number 1 and string ‘2’:
1 2 3 4 | SELECT 1 + '2' result FROM sysibm.sysdummy1; |
The result is number 3:
1 2 3 | RESULT ------- 3 |
Db2 has implicitly converted the string ‘2’ to an integer 2 due to the add operator (+).
The following example concatenates the number 1 with the string ‘2’ using the concatenate operator (||)
1 2 3 4 | SELECT 1 || '2' result FROM sysibm.sysdummy1; |
The result is as follows:
1 2 3 | RESULT ------ 12 |
In this example, Db2 implicitly converted the number 1 to the string ‘1’ and concatenated it with the string ‘2’.
By default, when you use values of different types in an expression or pass the values of different types to functions, Db2 will try to convert the value of one type to another, depending on the context.
If the conversion fails, Db2 will issue an error:
1 | Invalid character found in a character string argument of the function "DECFLOAT".. SQLCODE=-420, SQLSTATE=22018, DRIVER=3.69.56 |
To explicitly convert a value of one type to another, you use the CAST
expression with the following syntax:
1 | CAST(value AS data_type) |
In this syntax, you specify the value and the data type to which you want to convert the value.
Db2 CAST
examples
Let’s take some examples of using the CAST
expression.
1) Converting a decimal to an integer example example
This example uses the CAST
expression to convert a decimal to an integer:
1 2 3 4 | SELECT CAST(10.7 AS INT) result FROM sysibm.sysdummy1; |
Here is the result:
1 2 3 | RESULT ----------- 10 |
2) Converting a decimal to decimal with a smaller scale example
This example uses the CAST
expression to convert a decimal number from DEC(5,3)
to DEC(4,2)
:
1 2 3 4 | SELECT CAST(10.235 AS DEC(4,2)) result FROM sysibm.sysdummy1; |
The result is as follows:
1 2 3 | RESULT ------ 10.23 |
3) Converting a timestamp to a time example
The following example uses the CAST
expression to convert a timestamp to a time:
1 2 3 4 | SELECT CAST(CURRENT TIMESTAMP AS TIME) result FROM sysibm.sysdummy1; |
Here is the output:
1 2 3 | RESULT --------- 14:52:05 |
4) Converting a timestamp to a date example
The following example uses the CAST
expression to convert a timestamp to a date:
1 2 3 4 | SELECT CAST(CURRENT TIMESTAMP AS DATE) result FROM sysibm.sysdummy1; |
Here is the output:
1 2 3 | RESULT ----------- 2019-06-13 |
5) Converting a string to a date example
This example uses the CAST
expression to convert a string to a date:
1 2 3 4 | SELECT CAST('2019-06-25' AS DATE) result FROM sysibm.sysdummy1; |
The output is as follows:
1 2 3 | RESULT ------------ 2019-06-25 |
In this tutorial, you’ve learned how to use the Db2 CAST
expression to convert a value of one type to another.