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!
Server Routine
Moderators: chulett, rschirm, roy
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.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.
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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
thank you for your help
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!
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!
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.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.
Can you please give me a brief sketch of the routine.
Thanks!
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>