how to subtract the month from yyyymmdd

deesh · Post by **deesh** » Fri Jun 19, 2009 4:21 am

Hi friends,

Can any one help me how to susbtract month from YYYYMMDD. Please look a exaple below.

next order date CCNPOD = 20100225
interval CCMPMO = 6
result should be : 20090825

CCMPMO ---Decimal (2)
CCNPOD ---Decimal( 8 )

ArndW · Post by **ArndW** » Fri Jun 19, 2009 5:11 am

The DateFromDaysSince function is what you are looking, described on page 593 of the Parallel Job Developer Guide

deesh · Post by **deesh** » Fri Jun 19, 2009 5:36 am

ArndW wrote:The DateFromDaysSince function is what you are looking, described on page 593 of the Parallel Job Developer Guide ...

Thanks for your reply, This DateFromDaysSince is not correct for my logic, actually I want If my date is 20100225 from that I have to minus the 6 moths so the result is 20090825, Please look my example once.

vkhandel · Post by **vkhandel** » Fri Jun 19, 2009 6:50 am

the desired output could be achieved by following logic implemented in transformer stage stage variables written in following order -

SVarDate (varchar8 ) --> decimaltostring(CCNPOD,"suppress_zero")
SVarCounter (varchar 2) --> decimaltostring(CCMPMO,"suppress_zero")
SVarMonth (varchar 2)--> If SVarDate[5,2] - SVarCounter < 0 then SVarDate[5,2] + 12 - SVarCounter else SVarDate[5,2] - SVarCounter
SVarYear (varchar 4)--> If SVarDate[5,2] - SVarCounter < 0 then SVarDate[1,4] - 1 else SVarDate[1,4]

and on the output link derivation , just concatenate the above variables as
Result --> SVarYear:SVarMonth:SVarDate[7,2]

Sreenivasulu · Post by **Sreenivasulu** » Fri Jun 19, 2009 6:50 am

I do not think there is any such built-in function or routine in datastage.
You need to build a custom routine to get the solution. The custom routine would need to access database and get the result.

Regards
Sreeni

ArndW · Post by **ArndW** » Fri Jun 19, 2009 7:27 am

You need to specify your definition of what a month is. What about 2010-01-31? Since June doesn't have 31 days what do you do? Once your rules about months are set, you can use the DateFromDaysSince function. Otherwsise you could convert your date into 3 components, do your math on them, then put them back togethr to a date along the lines of what vkhandel has posted.