days in decimal form

[pooja]

how to get no of days differernce in decimal form, ..
say--12.75 days

from 2010-01-13;00.00.00
to 2010-01-01;04:55;12

thank you

[blom0344]

You're on DB2 aren't you? (from earlier post by almost the same name) Did you check SQL cookbooks for a native solution?
There is a specific TIMESTAMPDIFF function available in DB2.

By the way , you are refering to Timestamps , not Days in your example
(and why not keep all your issues concerning date calculations within the same post?)

[blom0344]

Elaborate, but the following seems to work:

[precision down to seconds]:

Code Select Expand


extract(day;current_timestamp  - _make_timestamp (2010;01;18))*24*3600+
extract(hour;current_timestamp  - _make_timestamp (2010;01;18))*3600+
extract(minute;current_timestamp  - _make_timestamp (2010;01;18))*60+
extract(minute;current_timestamp  - _make_timestamp (2010;01;18))


returns integer for value as differnce in seconds between timestamps