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
Will this help:
http://www.akadia.com/services/ora_date_time.html
- Alp