Updated March 6, 2023
Introduction to DB2 CAST
DB2 CAST is a function available in DB2 that is used for explicit conversion of the data type of a particular value to another datatype. DB2 comes with the default facility of trying to convert the datatypes of the values if they are not mentioned properly to an expected data type value in all the functions and the instructions that are issued and where there is a necessity of doing so. This is called implicit casting or conversion of a datatype. In this article, we will study how we can convert the value of one data type to another with the help of the CAST() function available in DB2, the syntax of the CAST() function, and the implementation with the help of certain examples.
Syntax
The syntax of the CAST() in DB2 is as shown below –
CAST(any expression or value AS the required datatype)
In the above expression, the expression or value can be any field value of a particular table column or a constant value, or even a variable that holds a certain value. The required data type is the data type that you want the value mentioned as the expression to be converted to. We can specify any data type, we want the data to convert to such as int, string, etc.
Implicit casting in DB2
Let us firstly have a look at how the DB2 DBMS converts the datatype of the expressions or values into some other required datatype directly internally with the help of certain examples. DB2 supports the usage of certain operations such as + for adding the numbers, || for concatenating the two values, etc. While using these operators it is necessary that the two values should be in the datatype for which the operator is supported. If the specified values are not in the required format they are internally converted by DB2 firstly and then the operation is performed on those values.
Examples
Consider the following example where we are trying to add 6 integer value with ‘16’ string value using the plus(+) operator and retrieve the result –
SELECT
6 + '16' as result ;
The execution of the above query statement gives the following output with the resultant value being an integer which is 22. Over here, DB2 firstly converted the string ‘16’ to an integer value and then went for doing the addition.
Let us consider one more example where we will be using the concatenation operator which is a string operator and works only with string. When we use the same values specified in the above example 6 and ‘16’ which are an integer and a string, using the following query statement –
SELECT
6 || '16' result ;
The execution of the above query statement gives the following output with the resultant value being a string which is 616. Over here, DB2 firstly converted the integer 6 to string value and then goes for doing the concatenation.
Now, let us see, how we can cast the values explicitly using the CAST() function. If the values cannot be cast by the DB2 due to incompatible type of values, it throws an error saying as shown in the below image –
Now, let us try to convert a decimal value to an integer value using the DB2 CAST() function which will do explicit datatype conversion for us. Consider the following query statement where we are trying to convert a decimal number 14.562 to and integer value to get a rounded whole number. We can do this by using the following query statement with the CAST() function in it –
SELECT
CAST(14.562 AS INT) result;
The output of the above query statement is a rounded integer value of 14.562 which is 14 as shown below –
Now, consider a decimal number 16.5454 which we want to cast to a decimal number itself but a lower scale value to it. We can even do this by using the CAST() function and our query statement, in this case, will look as follows –
SELECT
CAST ( 16.5454 DEC (4,2)) AS result;
The output of the above query statement’s execution is as shown below with the number rounded and cast to a decimal value of two places after a decimal point has a different scale than the original one –
Let us try to convert a value of the TIMESTAMP datatype to the TIME datatype, in order to get only the time value in the output. We will convert the current timestamp value to time using the cast function in the following query statement to retrieve the current time value of the system –
SELECT
CAST (CURRENT TIMESTAMP AS TIME) as result;
The execution of the above query statement gives the following result where we get the value of the current time of the system from the current TIMESTAMP value which is as shown below –
We can even convert the current timestamp value to the date datatype in order to retrieve today’s date of my system and using the following query statement and the CAST() function –
SELECT
CAST (CURRENT TIMESTAMP AS DATE) as result;
The output of the execution of the above query statement is as shown below with the date value in it which is the current system date.
Let us try one last example where we will cast the value of string data type to a DATE datatype explicitly by using the CAST() function in DB2 RDBMS. Let us consider a random date value say ‘2030-01-27’. We will try to convert this string to DATE datatype by using the following query statement –
SELECT
CAST('2030-01-27' AS DATE) result;
The output of the execution of the above query statement in DB2 DBMS gives the following resultant value with the date specified being cast to a DATE data type as shown below –
Conclusion
IN DB2 RDBMS, the datatypes are internally converted into required datatypes while using the functions and manipulations and while doing operations. However, if we want to explicitly convert a particular value to a required datatype then we can make use of the CAST() function to convert the value to a different datatype explicitly in DB2 RDBMS. We can convert the data type f the values to any in-built and user-defined datatype using the CAST() function.
Recommended Articles
This is a guide to DB2 CAST. Here we discuss how we can convert the value of one data type to another with the help of the CAST() function. You may also look at the following article to learn more –
