Date Calculations

new_ds_man · Post by **new_ds_man** » Mon Oct 05, 2009 12:38 pm

Hi All,

I need some help on some Date calculations.

My Requirement:

I have a month_number assigned to every month starting 'MAY-2009'.

Ex:

Create_Date, Month_Number

May-2009, 150
June-2009, 151
July-2009, 152
........... as so forth

I receive create date from source, which, say is today's date 2009/10/05, i need to calculate the current month number using the start date (May 2009) and Month number (150) that i have.

Help Appreciated.
Thanks in Advance.

chulett · Post by **chulett** » Mon Oct 05, 2009 12:46 pm

This 'month number' reference, where is it stored - in a database table? And the 'create month' is a string field? And are you saying there's more to this than just a lookup, that you have a starting month/number and you need to compute the result somehow? Or just convert '2009/10/05' to 'October-2009' and do a lookup to get 155?

new_ds_man · Post by **new_ds_man** » Mon Oct 05, 2009 1:29 pm

chulett wrote:This 'month number' reference, where is it stored - in a database table? And the 'create month' is a string field? And are you saying there's more to this than just a lookup, that you have a starting month/number and you need to compute the result somehow? Or just convert '2009/10/05' to 'October-2009' and do a lookup to get 155?

Hey Chulett,

The month number, it is not stored anywhere. we actually know the number for May 2009 as 150. We will be provided the create date and we need to deduce the month number for the provided date using the available date & number (may 2009, 150).
The calculation needs to be done in DS on the fly.

chulett · Post by **chulett** » Mon Oct 05, 2009 1:46 pm

Okay... what's stopping you from storing it somewhere, even if that somewhere is only a flat file, you could use it to do a lookup against. And a job would build the list one time, rather than having to do it 'on the fly' each time. Just curious...

ray.wurlod · Post by **ray.wurlod** » Tue Oct 06, 2009 12:07 am

The fact that YOU know it doesn't help DataStage!

It could be a calculation or a lookup.

For example

Code: Select all

InLink.TheYear - 2009 + 155

new_ds_man · Post by **new_ds_man** » Tue Oct 06, 2009 7:30 am

Thanks Guys, I am planning on getting this done thru a SQL by looking up the table. I will updated this thread once the issue is resolved.