END-OF_MONTH, ADD_MONTHS, NUM_OF_WEEKS

[gateleys]

given a date (say, 09/14/2005),
1. how to compute end of the month?
2. how to add/subtract 9 months from the above end of month?

Suppose, another date (say, 01/19/2005) is also given, how to compute the number of weeks between the two dates?

[ray.wurlod]

Welcome aboard! :D

Start by reviewing Transforms such as MONTH.LAST, MONTH.FIRST and so on.

Then search the forum for suggestions.

Get back if you're still struggling.

Adding weeks is easy. It's adding (7 * days). Since dates are stored internally as days (search for Iconv() function), this is simple arithmetic in DataStage.

[kumar_s]

Welcome aboard! :D

Start by reviewing Transforms such as MONTH.LAST, MONTH.FIRST and so on.

Then search the forum for suggestions.

Get back if you're still struggling.

Adding weeks is easy. It's adding (7 * days). Since dates are stored internally as days (search for Iconv() function), this is simple arithmetic in DataStage.

HI,
how about adding months including leap year constraints

regards
kumar

[ray.wurlod]

I have a bulletproof routine for doing that. Anyone who engages my professional services for DataStage is entitled to the suite, which includes AddDays, AddWeeks, AddMonths and AddYears (both to dates and to timestamps), fully documented and tested.

Essentially the algorithm is to add the months, adjusting year if necessary, then rewind the day number until a legal date is encountered. Plus some variation when the offset number is negative. Plus a whole lot of idiot-proofing to take account of invalid input arguments.

[gateleys]

Thanks Ray, I got the work done.

[quote="kumar_s"][quote="ray.wurlod"]Welcome aboard! :D

Start by reviewing Transforms such as MONTH.LAST, MONTH.FIRST and so on.

Then search the forum for suggestions.

Get back if you're still struggling.