asked on

DB2: Casting and substring issues

Hi Techies--

OK, it looks like there's an issue with leading "0's" with dates and months on a substring/cast conversion. The date is sitting in the PRTED8 field as decimal(8,0) like 12311995 - or December 31, 1995. That date will convert correctly to 12/31/1995 for "TermDate-Date" and 1995-12-31 for "TermDate-Varchar" If I have a date like 01311996 what I get is: NULL for "TermDate-Date" and 996-13-11 for "TermDate-Varchar" . What is the best way to solve this issue.

SELECT
CAST(SUBSTRING(CAST(PRTED8 AS VARCHAR( 8 )),5,4) || '-' || SUBSTRING(CAST(PRTED8 AS VARCHAR( 8 )),1,2) || '-' || SUBSTRING(CAST(PRTED8 AS VARCHAR( 8 )),3,2) AS VARCHAR(10)) AS "TermDate-Varchar",
CAST(SUBSTRING(CAST(PRTED8 AS VARCHAR( 8 )),5,4) || '-' || SUBSTRING(CAST(PRTED8 AS VARCHAR( 8 )),1,2) || '-' || SUBSTRING(CAST(PRTED8 AS VARCHAR( 8 )),3,2) AS DATE) AS "TermDate-Date",
DIGITS(PRTED8) AS "TermDate-Decimal"
FROM XXXTST.SSSSS

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Paula DiTallo

ASKER

Very nice-- Thank you. The only thing I would add, is that I had the idea to issue digits(decimal_value) before the casting as an alternative to '0' + cast(decimal_value

Kent Olsen

Hi ditallop,

The DIGITS function will work nicely, too. :)

Kent