Page 1 of 1

subtract month that a date field

PostPosted: Wed Feb 19, 2014 11:18 pm
by itamar rocha
Hello,
I have the problem below,

I have the field

dt-inicio pic x(8) format aaaammdd
dt-fim pic x(8) format aaaammdd

So I have to subtract the dt-inicio - dt-fim , it is ok.
But my problem is when I have to subtract one month that the result.

Anybody knows a way to subtract month that a date field?

Thanks

Re: Date.

PostPosted: Thu Feb 20, 2014 12:39 am
by Terry Heinze
There are many intrinsic fundtions that deal with dates and date calculations. You should find a solution in the Language Reference Manual.

Re: Date.

PostPosted: Thu Feb 20, 2014 1:01 am
by itamar rocha
Thanks for the answer,
I found one solution, is like these:

SELECT ADD_MONTHS('2007-02-28', 4)
INTO :WS-DATA-2

But I would like to increase the month with a working variable and the month too.
like this:
SELECT ADD_MONTHS(ws-date, ws-moth)
Is that is possible?

Re: Date.

PostPosted: Thu Feb 20, 2014 1:03 am
by enrico-sorichetti
everything is easy when subtracting/adding days

somebody should get in touch with your audit/legal/standards & practices/...
to know/understand/made aware/... of the rules for months/year related computation
whenever the resulting month has a different number of days

but it is strange that in AD 2014 You are the first in Your organization to face the problem
why not ask Your peers if there are already general use functions/subroutines in place ?

why did You add a db2/sql dependency to Your process ???

Re: Date.

PostPosted: Fri Feb 28, 2014 12:23 am
by itamar rocha
Hello,
I did a reseach and I found the example below:

01 WS-AUXILIARES.
05 WS-DIAS PIC 9(011) VALUE ZEROES.
05 WS-DIAS-1 PIC 9(008) VALUE 20060611.
05 WS-DIAS-2 PIC 9(008) VALUE 20050611.

COMPUTE WS-DIAS = FUNCTION INTEGER-OF-DATE (WS-DIAS-1)
DISPLAY 'INTEGER-OF-DATE DIAS - ' WS-DIAS

* Result : FUNCTION INTEGER-OF-DATE ........ - 00000148085

Is there any function that calculates the difference between two dates and the result is the difference in months between them?

Thanks again,

Re: Date.

PostPosted: Fri Feb 28, 2014 7:51 pm
by dick scherrer
Hello,

Yes. At the top and botto of the page is a link to IBM Manuals. The first set of manuals is for COBOL. In the Enterprise Programming Guide, do a search for DATE ARITHMETIC and you will have a very good starting point.

Re: subtract month that a date field

PostPosted: Mon Mar 10, 2014 6:20 pm
by itamar rocha
Thank for the answer,
But I need more help, my big problem is:

Example: When I have this fields below:

WS-DATA1 2014-05-01
WS-DATA2 2014-01-01

And I execute the command ok it is working.

EXEC SQL
SELECT MONTH(DATE(:WS-DATA1) - DATE(:WS-DATA2))
INTO :WS-MESES
FROM SYSIBM.SYSDUMMY1
WITH UR
END-EXEC

The WS-MESES result is : 4

My problem is when there is the difference about the year:
Example:

WS-DATA1 2014-05-01
WS-DATA2 2012-01-01

When I execute the command sql , the WS-MESES result continue 4, and it is not rigtht, because I want to know the
difference the months, The correct result could be : 28 months.

Is there a command that do this?
Thanks for the help

Re: subtract month that a date field

PostPosted: Mon Mar 10, 2014 6:49 pm
by enrico-sorichetti
as usual we waste time on incomplete information ...

do You (1) want just the month difference disregarding the days
or You (2) want to take into account also the days

(1) diff = (year1-year2)*12 + month1 -month2
2014/02/15 - 2014/01/30 == 1 month

(2)
2014/02/15 - 2014/01/30 ==> 0 months ???
2014/02/15 - 2014/01/02 ==> 1 MONTH ???

for a VERY DIRTY APPROACH You might want to consider something like
just the logic ( FIND OUT YOURSELF the RIGHT SNTAX)

( ( days1 - days2 ) /365.25 ) / 12

but until You describe EXACTLY the APPLICATION requirement we are just spinning around in circles

again ask the powers of Your organization for the rules
once the rules are clear also the algorithm will be

Re: subtract month that a date field

PostPosted: Mon Mar 10, 2014 8:41 pm
by itamar rocha
Thanks enrico-sorichetti for the answer.
But I found other solution, I use this command:

SELECT MONTHS_BETWEEN (:WS-DATA1,:WS-DATA2)
INTO :WS-MESES
FROM SYSIBM.SYSDUMMY1
WITH UR

And it is working
Thanks again

Re: subtract month that a date field

PostPosted: Mon Mar 10, 2014 9:25 pm
by NicC
But why use a resource intensive DB2 call instead of standard Cobol/LE or your own company's standard?