EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials DB2 Tutorial DB2 CAST
Secondary Sidebar
DB2 Tutorial
  • DB2 Tutorial
    • What is DB2?
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE

DB2 CAST

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.

Syntax

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

The syntax of the CAST() in DB2 is as shown below –

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,171 ratings)

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
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more