DateDifference

[prasanna2100]

is there any built in function to find the date difference of two DATE values

[Sainath.Srinivasan]

As you are using Server Edition, you can use IConv. Search for it.

[ray.wurlod]

Welcome aboard. :D

The Iconv() function itself does not calculate the date difference - it converts the date into DataStage internal format, which is the number of days from a particular day zero (31 Dec 1967). It follows that, if you subtract internal format dates, you get the difference in days.

[dprasanth]

is there any built in function to find the date difference of two DATE values

You can use the internal conversion as
Iconv(date1,"D/DMY") - Iconv(date2,"D/DMY")

[prasanna2100]

thanks for ur reply .but i need the result to be in YEARS

[dprasanth]

thanks for ur reply .but i need the result to be in YEARS

Why don't you try this
(Iconv(date1,"D/DMY") - Iconv(date2,"D/DMY"))/365

When you do Iconv(date1,"D/DMY") - Iconv(date2,"D/DMY"), you will get the diff in days, so divide it by 365,which will give you years

[prasanna2100]

great yaar its working !!

since my date is already in Internal format ,i just need to subtract it and convert it to years.

but y DS doesn't have a built in date diff function as all other ETL tools have.

[gateleys]

Yeah, try that with a number of leap years in between and post your result.

gateleys

[gateleys]

Oh! Didn't notice that you only wanted number of years. In that case, never mind my previous post, you are fine.

gateleys

[clshore]

Considering gateleys observation about leap years, what you must do is first extract the year portion of each date, and then subtract.

Also, consider how your application treats fractional year differences; do you 'round-up' or 'round-down'?

As for the built-in date difference function, since DataStage keeps dates as an internal number type, it's already there ( the minus sign ).

Carter

[ray.wurlod]

Code: Select all

YearsDiff = Int(DaysDiff / 365.249) 

is close enough to account for leap years in most cases.

[sb_akarmarkar]

Hi,

Try following formula:

Oconv((Iconv(Arg1[1,10],"D/MDY") - Iconv(Arg2[1,10],"D/MDY")),"D2 Y") - 67


This may help you out:

Thanks,

Anupam
akarmarkar@smart-bridge.co.in

[clshore]

Or more directly:

Ans = Oconv(Iconv(Arg1[1,10],"D/MDY"),"D4Y") - Oconv(Iconv(Arg2[1,10],"D/MDY")),"D4Y")


Carter