{"id":849,"date":"2019-06-13T00:34:58","date_gmt":"2019-06-13T08:34:58","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=849"},"modified":"2020-06-29T23:16:56","modified_gmt":"2020-06-30T07:16:56","slug":"db2-cast","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-basics\/db2-cast\/","title":{"rendered":"Db2 CAST"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 CAST<\/code> expression to convert a value of a type to another.<\/p>\n\n\n\n

Db2 CAST expression overview<\/h2>\n\n\n\n

The following query adds the number 1 and string ‘2’:<\/p>\n\n\n

SELECT<\/span>\n    1<\/span> + '2'<\/span> result<\/span> \nFROM<\/span>\n    sysibm.sysdummy1;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The result is number 3:<\/p>\n\n\n
RESULT                                     \n-------<\/span>\n3                      \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Db2 has implicitly converted the string ‘2’ to an integer 2 due to the add operator (+).<\/p>\n\n\n\n
The following example concatenates the number 1 with the string ‘2’ using the concatenate<\/a> operator (||)<\/p>\n\n\n
SELECT<\/span>\n    1<\/span> || '2'<\/span> result<\/span>\nFROM<\/span> \n    sysibm.sysdummy1;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The result is as follows:<\/p>\n\n\n
RESULT \n------ <\/span>\n12     \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, Db2 implicitly converted the number 1 to the string ‘1’ and concatenated it with the string ‘2’.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
If the conversion fails, Db2 will issue an error:<\/p>\n\n\n
Invalid character found in a character string argument of the function \"DECFLOAT\".. SQLCODE=-420, SQLSTATE=22018, DRIVER=3.69.56\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To explicitly convert a value of one type to another, you use the CAST<\/code> expression with the following syntax:<\/p>\n\n\n
CAST(value AS data_type)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you specify the value and the data type to which you want to convert the value.<\/p>\n\n\n\n
Db2 CAST examples<\/h2>\n\n\n\n
Let’s take some examples of using the CAST<\/code> expression.<\/p>\n\n\n\n
1) Converting a decimal to an integer example example<\/h3>\n\n\n\n
This example uses the CAST<\/code> expression to convert a decimal to an integer:<\/p>\n\n\n
SELECT<\/span> \n    CAST<\/span>(10.7<\/span> AS<\/span> INT<\/span>) result<\/span>\nFROM<\/span> \n    sysibm.sysdummy1;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the result:<\/p>\n\n\n
RESULT      \n----------- <\/span>\n10          \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
2) Converting a decimal to decimal with a smaller scale example<\/h3>\n\n\n\n
This example uses the CAST<\/code> expression to convert a decimal<\/a> number from DEC(5,3)<\/code> to DEC(4,2)<\/code>:<\/p>\n\n\n
SELECT<\/span> \n    CAST<\/span>(10.235<\/span> AS<\/span> DEC<\/span>(4<\/span>,2<\/span>)) result<\/span>\nFROM<\/span> \n    sysibm.sysdummy1;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The result is as follows:<\/p>\n\n\n
RESULT \n------ <\/span>\n10.23\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Converting a timestamp to a time example<\/h3>\n\n\n\n
The following example uses the CAST<\/code> expression to convert a timestamp<\/a> to a time<\/a>:<\/p>\n\n\n
SELECT<\/span> \n    CAST<\/span>(CURRENT<\/span> TIMESTAMP<\/span> AS<\/span> TIME<\/span>) result<\/span>\nFROM<\/span> \n    sysibm.sysdummy1;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
RESULT                    \n---------<\/span>\n14:52:05        \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
4) Converting a timestamp to a date example<\/h3>\n\n\n\n
The following example uses the CAST<\/code> expression to convert a timestamp<\/a> to a date<\/a>:<\/p>\n\n\n
SELECT<\/span> \n    CAST<\/span>(CURRENT<\/span> TIMESTAMP<\/span> AS<\/span> DATE<\/span>) result<\/span>\nFROM<\/span> \n    sysibm.sysdummy1;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
RESULT                    \n-----------<\/span>\n2019-06-13  \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
5) Converting a string to a date example<\/h3>\n\n\n\n
This example uses the CAST<\/code> expression to convert a string to a date:<\/p>\n\n\n
SELECT<\/span> \n    CAST<\/span>('2019-06-25'<\/span> AS<\/span> DATE<\/span>) result<\/span>\nFROM<\/span> \n    sysibm.sysdummy1;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is as follows:<\/p>\n\n\n
RESULT                    \n------------<\/span>\n2019-06-25          \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you’ve learned how to use the Db2 CAST<\/code> expression to convert a value of one type to another.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t