Any function to subtract 1month from the given date

[ravij]

Hi,

I am passing the business date through parameter to retrieve the data from DB2 db. Now I need to subtract 1month from the date(format is like YYYYMM, Ex: 200701).

Is there any way to calculate the above one?

Thanks in advance.

[ray.wurlod]

Yes.

Does not the Search menu option work for you?

[ravij]

Sorry Ray, I searched in the forum but I could not find any help in search... Please help me to solve this issue.

I appreciate your help

[chulett]

I tried searching for something silly - 'subtract month' - and easily found what you were looking for. You may still need to branch out from there as your definition of what 'one month' is can change the answer.

[ravij]

Hi,

Actually I need to subtract one month from the date parameter(business date ex: YYYYMM or 200702) in WHERE clause in DB2 stage. We have written USER DEFINED SQL in the db2 stage.

The code is like:

WHERE ACG_CGY_CODE in ('31520001', '31520002') and DOC_TP = 'SV' and MSR_PRD_ID = <business date YYYYMM> - 1

Please help to solve this issue.
Thanks in advance

[ravij]

Hi,

Actually I need to subtract one month from the date parameter(business date ex: YYYYMM or 200702) in WHERE clause in DB2 stage. We have written USER DEFINED SQL in the db2 stage.

The code is like:

WHERE ACG_CGY_CODE in ('31520001', '31520002') and DOC_TP = 'SV' and MSR_PRD_ID = <business date YYYYMM> - 1

Please help to solve this issue.
Thanks in advance

[ray.wurlod]

You've made it a DB2 question - surely you should be asking it in a DB2 forum somewhere. There is a perfectly good AddMonths routine available here but, no, you want to do it in SQL. Fine, go for it: it's no longer a DataStage question.

[chulett]

You don't need to do the subtraction in the SQL, although I'm sure that can be done. The routines you can find here would allow you to subtract a month from the parameter before it is passed to the job.

Ask your DBA how to do that in DB2 SQL.