Get the month

cyclogenisis · Post by **cyclogenisis** » Fri Jan 22, 2016 10:40 am

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.

chulett · Post by **chulett** » Fri Jan 22, 2016 10:44 am

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?

cyclogenisis · Post by **cyclogenisis** » Fri Jan 22, 2016 11:09 am

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.

chulett · Post by **chulett** » Fri Jan 22, 2016 11:32 am

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

cyclogenisis · Post by **cyclogenisis** » Fri Jan 22, 2016 11:44 am

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

ArndW · Post by **ArndW** » Fri Jan 22, 2016 11:55 am

Code: Select all

OCONV(ICONV(OCONV(DATE(),"DY/M[4,2]"):"/01","D/YMD[4,2,2]")-1,"D/YM[4,2]"):"/01"

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.

cyclogenisis · Post by **cyclogenisis** » Fri Jan 22, 2016 12:00 pm

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.

ArndW · Post by **ArndW** » Fri Jan 22, 2016 12:07 pm

(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.

cyclogenisis · Post by **cyclogenisis** » Fri Jan 22, 2016 12:16 pm

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

cyclogenisis · Post by **cyclogenisis** » Sat Jan 23, 2016 12:15 am

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

chulett · Post by **chulett** » Sat Jan 23, 2016 8:44 am

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.

cyclogenisis · Post by **cyclogenisis** » Sat Jan 23, 2016 8:26 pm

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.

chulett · Post by **chulett** » Sat Jan 23, 2016 10:34 pm

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".

cyclogenisis · Post by **cyclogenisis** » Sun Jan 24, 2016 1:53 am

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.

cyclogenisis · Post by **cyclogenisis** » Fri Feb 19, 2016 1:09 pm

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