Date calculation

etlgeek · Post by **etlgeek** » Sat Nov 10, 2007 8:18 am

Hi

I have two timestamps in the format yyyy-mm-dd hh24:mi:ss.
I want to find the exact difference (sec level). I am doing below code, but is only giving me the difference in number of days not up to the HH:MM:SS level. Can someone help me to modify the code?

code
Iconv(Field(date1," ",1),"D-YMD[4,2,2]") - Iconv(Field(date2," ",1),"D-YMD[4,2,2]")

date1 - date2

example:
2007-11-10 00:05:44 - 2007-10-26 09:50:42

results i am getting : 15 (days)

Thanks

chulett · Post by **chulett** » Sat Nov 10, 2007 8:55 am

It's alot more complicated than that. Split the timestamp and convert all four pieces. Then (Days * 24 * 60 * 60) + net time difference.

chulett · Post by **chulett** » Sat Nov 10, 2007 9:42 am

If you'd rather not have the joy of working this out for yourself, there is some code posted here you might find useful. :wink:

Oh, and welcome! :D

etlgeek · Post by **etlgeek** » Sat Nov 10, 2007 10:50 am

Thanks Chulett for quick reply. That routine looks more complicated. so if i modify the code as below to get the results in hours instead of no of days. does it give correct results in hours?

(Iconv(Field(source.COMPLETED_DATE," ",1),"D-YMD[4,2,2]") - Iconv(Field(source.ENTERED_DATE," ",1),"D-YMD[4,2,2]") )*24

for the same example i am getting 360.

Thanks

etlgeek · Post by **etlgeek** » Sat Nov 10, 2007 10:52 am

Thanks Chulett for quick reply. That routine looks more complicated. so if i modify the code as below to get the results in hours instead of no of days. does it give correct results in hours?

(Iconv(Field(source.COMPLETED_DATE," ",1),"D-YMD[4,2,2]") - Iconv(Field(source.ENTERED_DATE," ",1),"D-YMD[4,2,2]") )*24

for the same example i am getting 360.

Thanks

ray.wurlod · Post by **ray.wurlod** » Sat Nov 10, 2007 1:28 pm

You need to multiply by 24 after performing the subtraction. DataStage complies with the usual rules for arithmetic operator precedence.