Page 1 of 1
Server Routine
Posted: Mon Jan 10, 2005 2:52 pm
by waitnsee
I need to implement a routine to get the period start date and period end date for the given run date.
Say if my run date is any date in Jan 2005, the period start date will be March-01-2005 and my period end date will be March-31-2005. So, it will be for every run thereafter. (i.e., if my run date is any date in Feb 2005, the period start date will be April-01-2005 and my period end date will be April-30-2005).
Any help regards to the above functionality is appreciated.
Thanks!
Posted: Mon Jan 10, 2005 3:08 pm
by ArndW
Hello WaitNSee,
does that mean your date logic is that the period requested is the calendar month that is 2 months after the run date? If that is the case then you can specify OCONV(ICONV(OCONV(@DATE,"DM")+2,"DM"),"D2/E") to get the first date of the period and OCONV(ICONV(OCONV(@DATE,"DM")+3,"DM")-1,"D2/E") to get the last.
Posted: Mon Jan 10, 2005 3:22 pm
by ray.wurlod
Arnd's post makes the point that you need the generic specification of how you are defining your start and end dates. For example "start date is the first day of the calendar month following the calendar month following the calendar month in which the current date occurs".
With such a specification, the logic is easily transformed into an expression such as "last day of current month (MONTH.LAST transform) plus 1 (to give first day of next month), then last day of that month (MONTH.LAST again) plus 1".
Get your components together.
Code: Select all
RunDate (job parameter?)
InternalFormat Iconv(RunDate, "D...")
RunMonth (MONTH.TAG transform, needed by MONTH.LAST)
StartDate (see above)
At this point your StartDate is in DataStage internal format; depending on what you're going to do with it you may need a final Oconv() to get it into an external format.
thank you for your help
Posted: Mon Jan 10, 2005 4:22 pm
by waitnsee
Hello Ray,
Can you be more specific on the arguments that I declare inside the routine. In otherwords can you show me a skeleton of the server routine for the above mentioned functionality.
Thanks!
Posted: Mon Jan 10, 2005 4:31 pm
by waitnsee
ArndW wrote:Hello WaitNSee,
does that mean your date logic is that the period requested is the calendar month that is 2 months after the run date? If that is the case then you can specify OCONV(ICONV(OCONV(@DATE,"DM")+2,"DM"),"D2/E") to get the first date of the period and OCONV(ICONV(OCONV(@DATE,"DM")+3,"DM")-1,"D2/E") to get the last.
Yes thats correct. But the run date is not a fixed date. It may be either today, tomorrow or any day in February, March, April and so on. So, how do I declare a variable run date which is used to calculate the start date and end date of the period.
Can you please give me a brief sketch of the routine.
Thanks!
Posted: Mon Jan 10, 2005 6:09 pm
by ArndW
WaitNSee,
Ray suggested one method - use a parameter; that gives you absolute control over the dates. I used "@DATE" which uses the date of the job run. From what you've asked I think Ray's response plus the Start/End calculator transforms that I posted should be enough to let you put the logic into your job.