No of years

srimitta · Post by **srimitta** » Thu May 31, 2007 3:39 pm

Hi All,

Any idea as how to find no.of years between two dates using in DS server.

START_DT END_DT
09/05/1966 00:00:00 06/22/1984 00:00:00
07/24/1964 00:00:00 12/03/1972 00:00:00
05/31/2004 11:32:22 02/02/2006 08:22:10

I tried to play with Oconv / Iconv but I couldn't.

Thanks
srimitta

chulett · Post by **chulett** » Thu May 31, 2007 3:42 pm

What do you consider to be the 'number of years' between any two dates? Simply the subtraction of the two YYYY components? The number of days between them divided by 365? Something else?

ArndW · Post by **ArndW** » Thu May 31, 2007 3:46 pm

The ICONV function has several options that can convert the data appropriately, depending upon your definition of "year" as noted in Craig's post. You can convert the date into the internal format (using the 10 character substring and format of "D4/MDY" and then use the difference in days or just use the year substring in the original text and compute a difference.
If you have difficulties with OCONV and ICONV, please post which combinations you have tried.

srimitta · Post by **srimitta** » Thu May 31, 2007 4:11 pm

Craig,
I have to consider Month, Day and Year to derive no.of years between two dates.

Arnd,
I havent' really developed any code but I was looking at Oconv/Iconv and conversion codes logically.

Thanks
srimitta

chulett · Post by **chulett** » Thu May 31, 2007 4:16 pm

Doesn't really answer my question. Write out a specification, spell out in words how the 'number of years' difference between two full dates would need to be computed, regardless of the tool you would use. Post it here.

Then we can help convert that into what you would need for a DataStage routine or derivation.

srimitta · Post by **srimitta** » Thu May 31, 2007 4:40 pm

Sorry if I am not clear,

ex:
05/28/1964 00:00:00 - 05/28/2004 00:00:00 = 40 years

My requirement:

1. Derive no.of years between two dates
2. Process data if no.of years between two dates are <= 30 years.

I am able to do this writing SQL sub-query, but there are chances we may swith source databases and client want to do this in DS.

Thanks
srimitta

ArndW · Post by **ArndW** » Thu May 31, 2007 5:13 pm

Srimitta,

it looks like you haven't experimented yet, but what would be wrong with:

Code: Select all

In.Column[29,4]-In.Column[7,4]

chulett · Post by **chulett** » Thu May 31, 2007 5:33 pm

srimitta wrote:1. Derive no.of years between two dates

Again, way too vague. Derive how exactly?

And unfortunately your example is a poor choice because the MM/DD portions are identical. How about these two dates - how many years are between them in your environment?

05/28/1964 & 03/27/1965

1? 2? Rounding up or down? Or should it be some fractional value between 1 and 2? To how many decimal places?

srimitta · Post by **srimitta** » Thu May 31, 2007 5:52 pm

ArndW,

1. As I said earlier I haven't developed any code. but I was looking at
Oconv/Iconv and conversion codes logically.

2. In.Column[29,4]-In.Column[7,4] will give me no.of years on YEAR part
of two dates.

3. 05/28/1994 - 09/28/1964 = 30 years / 4 monts
If I substract on year part I would get only no.of years and I would be
missing months.

4. I cn't ignore months and day as anything 30 years and above even it's
1 day or 1 mont will be treated above 30 years.

5. I am looking for something to substract on year, month and day part
between two dates.

Thnaks
srimitta

ray.wurlod · Post by **ray.wurlod** » Thu May 31, 2007 7:32 pm

Search for AddYears or AddMonths. You will find a link to a dsx file on my website with several date manipulation routines of this type.

ArndW · Post by **ArndW** » Thu May 31, 2007 7:40 pm

Srimitta,

do you now understand why all the other posters are asking you for your definition of "years"? I posted my snippet knowing that it would be incorrect, but at least it did require you to define in more detail what you need.

If you use ICONV() on both dates the difference between them will be the number of days separating the two dates. If you can afford to ignore leap years, then dividing by 365 will give you the number of years between the two events, and then you can use the remainder (or use MOD()) to choose whether to round up by one year or not.

roy · Post by **roy** » Sun Jun 03, 2007 11:13 pm

Hi,
Assuming you'll send two arguments to a basic routine your code would do something like the following example
Let's say your arguments are:
firstDate = 05/28/1994
secondDate = 09/28/1964

Code: Select all

yearsDiff = firstDate[7,4] - secondDate[7,4]
monthsDiff = firstDate[4,2] - secondDate[4,2]
daysDiff = firstDate[1,2] - secondDate[1,2]
If daysDiff < 0 Then monthsDiff  = monthsDiff -1
If monthsDiff < 0 Then yearsDiff = yearsDiff -1

I wrote from memory, so you need to test and debug...
IHTH (I Hope This Helps),