DSXchange

Hi,

I have the following function I use which gets me yesterday's date in format YYYYMMDD:

Oconv(Date() - 1), "DYMD[4,2,2]" : @VM : "MCN")

I would like to use something similar but, I would like it to return the previous month from today's Date. To be in the same format YYYYMMDD.

Example, today is 20160122

Result would be: 20151201 (yes would like to default it to the first day of the previous month)

Another example
Current date is : 20160318
Result: 20160201


Thanks for any help ahead of time.

That code would be appropriate for either a Server job or a routine, yet you've marked the post as Parallel and posted in the Parallel forum. So... are you looking for a native PX solution?

Right now that code is being used in a user variable stage in a sequence job. Will be using this solution in exact same stage.

That's an important piece of information, let's get you in the correct forum for this...

Ok thanks, any idea on how this can be done?

Take today's date and convert it to YYYYMM and add "01" to get the first day of the current.
Then convert it back to internal format and subtract a day to get the last day of the previous month.
Convert this to a YYYYMM string and add "01" to get the first day of the previous month.

Would there not be an easier way using MONTH.FIRST. I only bring this up from other threads I'm reading.

Also now that I'm thinking about it I would need to be able to specific how many months to go back. I may need to reload anywhere from 1 month to 12 months ago. I can use a loop counter variable for this if there is the ability to subtract a month using the counter.

(Note, I edited my BASIC code from the original post due to an error, the code now in the thread works)

I hadn't thought about MONTH.FIRST, but it wouldn't completely remove ICONV/OCONV here, it could give you the first day of the current month, but then you'd still have subtract a day and convert the format.

Hi ArndW, does this code allow for a loop counter variable to be added some where so I can say how many months to go back (1 being previous, 2 being 2 months etc..)? I'm subscribing to premium right now to view your answers

Bump, looking for a way so I can look through months using loop counter variable.

Not sure why you keep mentioning a 'loop counter' as there's no need to loop to go back more than one month. So you just need an offset to drive how many months to go back? I'd suggest you go to Ray's site (coming soon!) and click on any of his download links. Cancel that download (or add it to your Bag O' Tricks) and then download his Date Routines dsx export file. Importing it will put a plethora of "date math" routines into your project, including one to AddMonths which sounds like one you could easily leverage here.

Hi chullet, i'm adding in another requirement. So if I want to use a loop to load more than 1 month starting with the previous month then 2 months back, then 3 months back etc.

Okay... doesn't really change what you need to do or how I would have answered. And your "1 then 2 then 3" is just "-1 three times".

Thanks Craig, I'll check out the routines ray has posted. I'm newer to datastage and haven't used routines before so something to explore.

The routine is working very well. Thanks Craig & Ray.