Oracle Date calculations

[cognosjon]

Afternoon all,

I'm after a bit of SQL help if possible.

I'm having fun conducting date time differences within Oracle. The code that I am using is working for me 90% of the time but then for some unknown reason to me starts
throwing its dummy out of the pram and gives odd answers.


When I use the following piece of SQL within Cognos I get the correct answers, but when I use it within Oracle SQL Developer to validate the results for testing I get
a mixture of good and bad results


The SQL code i'm currently working with is as follows:


SELECT DISTINCT act_start_date, act_end_date, CEIL (extract(day from 86400*(act_end_date-act_start_date)/60/60))

FROM F_ACTIVITY

WHERE act_end_date BETWEEN ('01-JAN-09 00.00.00.000000000') and ('05-JAN-09 23.59.000000000')

GROUP BY act_start_date, act_end_date

ORDER BY act_end_date


The answers, both good and bad I'm getting are shown below.


Start Date                End date                      hours between         Test Pass
03-Jan-09 10:20.00        05-Jan-09 18:20.00                  8                  Yes
03-Jan-09 07:19.00        03-Jan-09 17:20.00                  7                  No


I'll admit to being no SQL guru so apologies for the rough edges of the above sample code, but can anyone see why I may be experiencing the issue? I'm happy with the
results being generated by Cognos but I can't validate my testing, which isn't very helpful.

Any help appreciated on this one....


thanks

[Alp]

Will this help:
http://www.akadia.com/services/ora_date_time.html

- Alp