CAST expression to convert data type – SQL

CAST expression is used to convert one data type to another. It is similar to the various field-type functions (e.g. CHAR, SMALLINT) except that it can also handle null values and host-variable parameter markers.

CAST syntax:

CAST( <Expression> AS Type [ (Length) ] )
      <NULL>
      <Parameter Maker> 

CAST Rules:

• EXPRESSION: Value for conversion to another data type. If the input is neither null, nor a parameter marker, the input data-type is converted to the output data-type. Truncation and/or padding with blanks occur as required. An error is generated if the conversion is illegal.
• NULL: If the input is null, the output is a null value of the specified type.
• PARAMETER MAKER: This option is only used in programs and need not concern us here. See the DB2 SQL Reference for details.
• TYPE – data type to which you want to convert the expression. It can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image.
• LENGTH – is optional. The length of the resulting data type for char, varchar, nchar, nvarchar, binary, and varbinary.

Note: When converting from float or numeric to integer, the CAST function truncates the result. For other conversions, the CAST function will round up the result.

Convert Decimal to Integer

SELECT 
     CAST(123.45 AS INT or INTEGER)
FROM 
     sysibm.sysdummy1;

Result: 123 

The requirement is to have the integer portion of the number (defined as decimal(9,2)).

Input:
099 999.99
101 123.99
100  99.99

SELECT EMPNO, CAST(SALARY AS INTEGER) 
FROM EMPLOYEE
WHERE EMPNO > 100
ORDER BY EMPNO

Result:
100   99.99
101  123.99 

Let’s say we are interested in the integer portion of the SALARY (defined as decimal(9,2)) from the EMPLOYEE table where the EMPNO is >100. The following query, including the employee number and the integer value of SALARY, could be prepared.

Convert decimal to decimal with a smaller length

SELECT 
     CAST(123.12345 AS DEC(4,2))
FROM 
     sysibm.sysdummy1; 

Result: 123.12 

CAST expression to convert a decimal number from DEC(5,5) to DEC(4,2)

CAST expression to truncate Char field

Input:
EMPNO  JOB
  10    Mgr  
  20    Sales
  99    IT
 100    Marketing
 

SELECT EMPNO
      , JOB 
      , CAST(JOB AS CHAR(3)) AS NEWJOB
FROM EMPLOYEE
WHERE EMPNO < 100
ORDER BY id; 

 Result:
 EMPNO     JOB            NEWJOB
 10        Mgr            Mgr
 20        Sales          Sal
 99        IT             IT 

Convert timestamp to time or Date

CAST expression to convert a timestamp to a time

SELECT 
     CAST(CURRENT TIMESTAMP AS TIME)
FROM 
     sysibm.sysdummy1; 

RESULT: 23:59:59 

CAST expression to convert a timestamp to a date

SELECT 
     CAST(CURRENT TIMESTAMP AS DATE)
FROM 
     sysibm.sysdummy1;
 
RESULT: 2021-07-01   

Convert SMALLINT field with null values

Input:
EMPNO  JOB
  10    Mgr  
  20    Sales
  99    IT
 100    Marketing
 
SELECT EMPNO
      , CAST(NULL AS SMALLINT) AS NEWVAL
FROM EMPLOYEE
WHERE EMPNO < 100
ORDER BY EMPNO;
 
Result:
EMPNO     NEWVAL           
 10        -           
 20        -         
 99        -     

Convert date to string using CAST() function

Syntax: CAST(date AS string) 

The date can be a literal or an expression that evaluates to a DATE value. The string can be any character string data type such as VARCHAR or TEXT. The CAST() function returns a string that represents the date. The following statement returns the current date and time as a date and as a string:

SELECT CURRENT_TIMESTAMP 'date',
    CAST(CURRENT_TIMESTAMP AS VARCHAR) 'date as a string'; 

Convert date to string using TO_CHAR() function

You can use the TO_CHAR() function to format a date as a string.

Syntax: TO_CHAR(value, format); 

The following example uses the TO_CHAR() function to format the current date using the YYYY-MM-DD

SELECT 
     TO_CHAR(SYSDATE, 'YYYY-MM-DD') 
FROM 
     sysibm.sysdummy1; 

Conclusion

IN DB2, 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.

CASE Statement: Click Here IBM DB2 Manual: Click Here