EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials DB2 Tutorial DB2 CAST
 

DB2 CAST

Updated March 6, 2023

DB2 CAST

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

DB2 CAST output 1

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.

DB2 CAST output 2

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 –

DB2 CAST output 3

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 –

output 4

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 –

output 5

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 –

output 6

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.

output 7

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 –

output 8

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 –

  1. What is DB2?
  2. DB2 Interview Questions
  3. Database Management Software
  4. Data Masking Tools

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW