datetime difference

bhaskarjha · Post by **bhaskarjha** » Thu Apr 27, 2006 3:07 am

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.

sb_akarmarkar · Post by **sb_akarmarkar** » Thu Apr 27, 2006 4:24 am

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

sb_akarmarkar · Post by **sb_akarmarkar** » Thu Apr 27, 2006 5:12 am

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

bhaskarjha · Post by **bhaskarjha** » Thu Apr 27, 2006 6:19 am

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.

DSguru2B · Post by **DSguru2B** » Thu Apr 27, 2006 6:41 am

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.

kcbland · Post by **kcbland** » Thu Apr 27, 2006 7:06 am

This should help
viewtopic.php?t=85788

DSguru2B · Post by **DSguru2B** » Thu Apr 27, 2006 7:08 am

Wow Ken, thats the solution right there. Thanks for pointing that out to us.
Cheers.

sb_akarmarkar · Post by **sb_akarmarkar** » Thu Apr 27, 2006 11:02 pm

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