How can I subtract one month of a date in a SERVER JOB?

igorbmartins · Post by **igorbmartins** » Fri Sep 17, 2010 7:26 am

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Sep 17, 2010 7:45 am

Does 1 month imply 30 days or same date previous month ? What if it is 30th March ?

If it is only 30 days, you can do

IConv(Date,'J')

and subtract 30.

igorbmartins · Post by **igorbmartins** » Fri Sep 17, 2010 8:50 am

I want to do something similar to ORACLE add_months:
select Add_months(sysdate, -1) from dual

chulett · Post by **chulett** » Fri Sep 17, 2010 9:11 am

Which doesn't really answer the question, especially for people without Oracle experience. Specifically, how do you define a month? As asked, what does that Oracle function give you when you subtract 1 month from March 30th?

igorbmartins · Post by **igorbmartins** » Fri Sep 17, 2010 11:58 am

select Add_months(sysdate, -1) as DATE1, sysdate as DATE2 from dual

DATE1= 2010-08-17
DATE2=2010-09-17

igorbmartins · Post by **igorbmartins** » Fri Sep 17, 2010 11:59 am

$INCLUDE DSINCLUDE DSJ_XFUNCS.H

DATA_FIM = oconv(Date(),'D.YMD[4,2,2]')
ANO=SUBSTRINGS(DATA_FIM ,1,4)
MES=SUBSTRINGS(DATA_FIM ,6,2)

* ARRUMA ANO
IF MES = '01' THEN
ANO = ANO -1
END

* ARRUMA MES
IF MES = '01' THEN
MES = '12'
END
ELSE
MES = MES-1
END

IF MES < 10 THEN
MES=0:MES
END

*COMPOE DATA
DIA=21
DATA_INICIO=ANO:'.':MES:'.':DIA

Call DSLogInfo("DATA_INICIO= ": DATA_INICIO,"JOB CONTROL")
Call DSLogInfo("DATA_FIM = ": DATA_FIM ,"JOB CONTROL")

Call DSSetUserStatus(DATA_INICIO:';':DATA_FIM )
Ans=DATA_INICIO:';':DATA_FIM

ray.wurlod · Post by **ray.wurlod** » Fri Sep 17, 2010 2:55 pm

You need also to handle dates such as March 30.

Check out the date routines in this file

jeawin · Post by **jeawin** » Fri Sep 17, 2010 4:42 pm

I have a routine to add days, months or years to a date, which correctly handles month end dates (for instance 28/02 + 1 month = 31/03). It will obviously need tweaking to subtract dates, but the principle is there.

If you are interested send me a personal email address and I'll send you the DataBasic code.

ray.wurlod · Post by **ray.wurlod** » Sat Sep 18, 2010 2:39 am

Mine do allow you to add negative offsets.