No of years
Moderators: chulett, rschirm, roy
No of years
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
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
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.
If you have difficulties with OCONV and ICONV, please post which combinations you have tried.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
Then we can help convert that into what you would need for a DataStage routine or derivation.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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
Srimitta,
it looks like you haven't experimented yet, but what would be wrong with:
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]
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Again, way too vague. Derive how exactly?srimitta wrote:1. Derive no.of years between two dates
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
I wrote from memory, so you need to test and debug...
IHTH (I Hope This Helps),
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
IHTH (I Hope This Helps),
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org