Midrange News for the IBM i Community


Posted by: raynesnf
Need help converting Date field to numeric field
has no ratings.
Published: 30 Jun 2016
Revised: 01 Jul 2016 - 2855 days ago
Last viewed on: 25 Apr 2024 (2914 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Need help converting Date field to numeric field Published by: raynesnf on 30 Jun 2016 view comments(3)

Hi Everyone,  I'm having an issue trying to convert a date field to a numeric field.  Actually I'm taking a numeric field in MMDDCCYY, converting to a date CCYY-MM-DD, adding 120 days to the date and then trying to convert it back to a numeric field in MMDDCCYY format.  Everything works fine except for the final conversion.  I've found a couple of examples on the web on how to convert from date to numeric and I've tried them, but it's not working.  Here's my code:

D  Hlddat         S          D

    Hlddat = %date(S3crap:*usa);                                S3crap = 06302016

    Hlddat = Hlddat + %DAYS(120);                            

    S3cexp = %dec(%char(Hlddat:*usa):8:0;                Hlddat = '2016-10-28'

When this runs, the last statement gives an error message "A character representation of a numeric value is in error".  But to me, the Hlddat value is correct.  Is there something that I'm not seeing?

Thank you for any help you can provide.

Traci

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: Ringer
Premium member *
Comment on: Need help converting Date field to numeric field
Posted: 7 years 9 months 25 days 11 hours 50 minutes ago

Hlddat += %DAYS(120);       

S3cexp = %dec(Hlddat:*USA) ;

Ringer

 

Posted by: BrianR
Premium member *
Green Bay, WI
Comment on: Need help converting Date field to numeric field
Posted: 7 years 9 months 25 days 9 hours 29 minutes ago
Edited: Fri, 01 Jul, 2016 at 11:02:04 (2855 days ago)

If you wanted to code this all in one line and get rid of the work variable:

S3cexp = %dec((%date(S3crap:*usa) + %DAYS(120)):*usa);

Posted by: bobcozzi
Site Admin ****
Chicagoland
www.cozTools.com/qryfile
Comment on: Need help converting Date field to numeric field
Posted: 7 years 9 months 25 days 5 hours 16 minutes ago

The problem is you are converting a date field to character and then attempting to convert that character field to numeric. However, *USA format in character form looks like this: "10/28/2016" therefore %DEC can not convert it, because of the dashes. You want to avoid the inner %CHAR(hlddat) and just convert the date field using %DEC with the optional 2nd parameter of the format in which you would like to return the date.