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.