Looping through a range of dates

shankar_ramanath · Post by **shankar_ramanath** » Thu May 07, 2009 8:49 pm

I have a requirement to increment date values where the date is in the format YYYYMMDD.

I have created a BASIC routine that achieves this functionality. For example, when I pass in a value of 20090531 I get an output of 20090601.

The use case scenario is that I would be provided with a range of dates in the input via two job parameters: FromDate and ToDate. I need to call a job activity for each date between FromDate and ToDate. For example, if the value of FromDate is 20090531 and the value of ToDate is 20090602, the job activity would be call thrice:once for 20090531, again for 20090601 and for 20090602.

I started working with the Start Loop activity but I could not achieve the desired functionality. I read through the forums and I thought that I could achieve this using a UserVariable stage. However, I cannot because I do not know where to provide the value of the output provided by the routine.

I could not use the value in "Step" in Start Loop activity. If I do this and provide a value of FromDate=20090531 and ToDate=20090602, the looping is not effected because 20090531 + 20090601 > 20090602. I could use it in the parameter for the Job Activity, but the result is not desired. If I use, Start_Loop_Activity.$Counter as the argument, with FromDate=20090531 and ToDate=2009602 and Step=1, the loop is repeated for 20090531, 20090532 and so on although the argument to the job activity is 20090601 always.

I would appreciate your help!

Many thanks

ray.wurlod · Post by **ray.wurlod** » Thu May 07, 2009 9:45 pm

The counted loop is only for integers. You may wish to convert your dates upstream to an internal, or Julian, format.

datastage_learner · Post by **datastage_learner** » Thu May 07, 2009 10:10 pm

calculate the total no. of days and run loop activity counter for total no. of days that you get. That may solve your issue.

shankar_ramanath · Post by **shankar_ramanath** » Fri May 08, 2009 12:10 am

[quote="datastage_learner"]calculate the total no. of days and run loop activity counter for total no. of days that you get. That may solve your issue.[/quote]

The issue with this approach is that the date is of a non-standard format and hence needs conversion.

I will work on Ray's suggestion.

Thanks all!

shankar_ramanath · Post by **shankar_ramanath** » Fri May 08, 2009 2:06 pm

Thanks Ray! I could use IConv and OConv to perform the looping

1. I used two user variables

fromdate = Iconv(FromDate,"D YMD")
todate = Iconv(ToDate,"D YMD")

2. Used a Start Loop (sa) to loop from "fromdate" to "todate" with Step=1.

3. Used OConv to retrieve the date value when calling the job activity
Trim(Oconv(sa.$Counter,"D YMD[4,2,2]")," ","A")