converting date into char in AS400

Question

Hi everyone ,

I am trying to convert the current date - 2 months into format yyyymmdd and subtract 1900000 from it.

Oracle was easy for me

SELECT  TO_CHAR(ADD_MONTHS(SYSDATE,-2),'YYYYMMDD') - 19000000 FROM DUAL 

Can some one suggest me the same for AS400 .

Thanks in advance.

Answer 1

EVAL numericField = %dec(%date(alphaDate:*MDY/):*MDY)

The parm following the char field must be the format of the char field (I used *MDY with "/"for an example).

The parm at the end of the expression must be the format of the numeric field. So this example converts 11/22/2008 to 11222008.

If you want 2008-11-22 converted to 20081122 make both parms *ISO.

Answer 2

SELECT DEC(SUBSTR(CHAR(DATE(NOW()) - 2 MONTHS), ISO), 1, 4)|| SUBSTR(CHAR(DATE(NOW()) - 2 MONTHS), ISO), 6, 2)|| SUBSTR(CHAR(DATE(NOW()) - 2 MONTHS), ISO), 9, 2), 8, 0) - 19000000 FROM DUAL

Answer 3

That should do the trick:

select char(((year(d)-1900) * 100 + month(d)) * 100 + day(d))    
from (select (curdate() - 2 months) as d from sysibm/sysdummy1) s

or if you prefer to do the same without nested select:

select char(((year(curdate() - 2 months) - 1900) * 100 
        + month(curdate() - 2 months)) * 100 
        + day(curdate() - 2 months))   
from sysibm/sysdummy1

Cheers