Many databases are designed in such a way that where a start/end time are stored there is no corresponding duration value, this is to avoid obvious data duplication and storage space as the duration can be calculated by comparing the start/end times. However some novice SQL coders struggle to calculate durations.
In Microsoft SQL there is the datediff function however this is not present in Oracle so the most straight forward method is to subtract the start date from the end date, this produces the difference expressed fractions of a day (i.e. an hour is expressed as 0.41677777), multiplying the number by 24 then gives the figure in hours.
(END_DATE – START_DATE) * 24
An easy way check your logic is to use a value within a dual statement such as the one below, obviously including the dates you are anticipating so that you can be sure of what figure to expect. This saves considerable time than sticking a date comparison into your where clause and crossing your fingers!
Select
(TO_DATE('01/08/2012 13:00','dd/mm/yyyy hh24:mi') - TO_DATE('01/08/2012 10:00','dd/mm/yyyy hh24:mi')) * 24 Difference_hours,
(TO_DATE('31/07/2013','dd/mm/yyyy') - TO_DATE('01/08/2012','dd/mm/yyyy')) Difference_Days
From dual
No comments:
Post a Comment