DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Views
    • Triggers
    • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
Home » Db2 Basics » Db2 CAST

Db2 CAST

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.

  • Was this tutorial helpful ?
  • YesNo
Previous Tutorial: Db2 COALESCE

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • What is Db2
  • Db2 LOCATE
  • Db2 RTRIM
  • Db2 LTRIM
  • Db2 INITCAP

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy

Copyright © 2019 · by www.db2tutorial.com. All Rights Reserved.

⤒