Page 1 of 1
datetime difference
Posted: Thu Apr 27, 2006 3:07 am
by bhaskarjha
With source as oracle and target as oracle , i am not able to pass the date , its going NULL in the target,
ALSO with INPUT as 12/18/2001 10:10:44 PM and 12/18/2001 10:10:54 PM , how to get the difference in days or in minites.
Posted: Thu Apr 27, 2006 4:24 am
by sb_akarmarkar
Hi,
From database itself you can get days difference by writing user defined query oracle source. Or using ICONV and OCONV with "MTH", You can get time difference in Datastage.
Thanks,
Anupam
Posted: Thu Apr 27, 2006 5:12 am
by sb_akarmarkar
Use following to get time in min
Arg1 and Arg2 is time ex: "02:34 am"
Result=Oconv((Iconv(Arg1[1,8],"MTH") - Iconv(Arg2[1,8],"MTH")),"MTH")
Ans= (substrings(Result,1,2) * 60) + substrings(Result,4,2)
Thanks,
Anupam
Posted: Thu Apr 27, 2006 6:19 am
by bhaskarjha
Thanks Anupam, for the solution.
I am looking for the difference in dates , say 12/31/2002 6:00:00 PM and 12/31/2002 6:00:00 PM and 19/31/2002 6:30:00 AM where both r having datatype as timestamp and target is having datatype as NUMBER (here difference will be in MInutes)
Can u plz give the Solution for this.
Posted: Thu Apr 27, 2006 6:41 am
by DSguru2B
You need to right a custom routine for that. First Iconv the date part from both timestamps, check the difference. If the difference is <> 0 then multiply that with 24*60 to get the minutes diff. in the dates. then Iconv the time part and get the diff. and divide that by 60.
Add the two numbers and thats your difference between the two timestamps.
Posted: Thu Apr 27, 2006 7:06 am
by kcbland
Posted: Thu Apr 27, 2006 7:08 am
by DSguru2B
Wow Ken, thats the solution right there. Thanks for pointing that out to us.
Cheers.
Posted: Thu Apr 27, 2006 11:02 pm
by sb_akarmarkar
bhaskarjha wrote:Thanks Anupam, for the solution.
I am looking for the difference in dates , say 12/31/2002 6:00:00 PM and 12/31/2002 6:00:00 PM and 19/31/2002 6:30:00 AM where both r having datatype as timestamp and target is having datatype as NUMBER (here difference will be in MInutes)
Can u plz give the Solution for this.
Using this function you will get number of day , Convert it into minute
Iconv(Arg1[1,10],"D/MDY") - Iconv(Arg2[1,10],"D/MDY")
Thanks,
Anupam
akarmarkar@smart-bridge.co.in