Database
Not applicable

## Date duration issue

Hi everyone,

Can anyone help me out here? This code obtains the duration between two dates by Month and Days. It does this by firstly extracting the difference between months. If the month day of the start date is greater than the month day of the end date, the number of months between the dates is reduced by 1. Following this, the code then retrieves the number of days from the month before the month of the end date and subtracts the difference between the month day start day and month day end date from it.

E.G.     Start Date: 27/09/2012   End Date: 14/11/2012

Month difference (end_start_diff) = 2

27 (month day of start date) > 14 (month day of end date) therefore:

Month difference = 1

Number of days in month preceding month of end date = 31 (October)

Difference between month day of start date and month day of end date = 27 – 14 = 13

Difference between number of days in month preceding month of start date and difference between month day of start date and month day of end date = 31 – (27 – 14) =  18

Day difference = 18

The code works fine for the example above but comes across issues when the month day of the start date is greater than the number of days of the month preceding the end date month AND when the month day of the end date is 1.

E.G.     Start date: 2012-12-31    End Date: 2013-07-01

Month difference (end_start_diff) = 7

31 (month day of start date) > 1 (month day of end date) therefore:

Month difference = 6

Number of days in month preceding month of end date = 30 (June)

Difference between month day of start date and month day of end date = 31 – 1 = 30

Difference between number of days in month preceding month of start date and difference between month day of start date and month day of end date = 30 – ( 31 – 1) =  0

Day difference = 0 ß actually when day difference = 0, the results actually produce the number of days in the month 2 months preceding the end date: End date month = July, Day difference month = May = 31

Any ideas here would be appreciated! J

CODE BELOW

,RS3RS_END_DATE - RS3RS_START_DATE MONTH(4) AS END_START_DIFF                                                                                                                                   -- Extracts months between dates

,CASE                                                                                                                                                                                                                                                                                                 -- if day of start date > day of end date then month - 1

WHEN RS3RS_END_DATE GE RS3RS_START_DATE THEN

CASE

WHEN EXTRACT(DAY FROM RS3RS_START_DATE) GT EXTRACT(DAY FROM RS3RS_END_DATE) THEN

CAST(END_START_DIFF AS DEC(10,0)) -1

ELSE CAST(END_START_DIFF AS DEC(10,0))

END

END AS GAP_M

,CASE                                                                                                                                                                                                                                                                                                 - Extracts days between dates

WHEN RS3RS_END_DATE GE RS3RS_START_DATE THEN

CASE

WHEN EXTRACT(DAY FROM RS3RS_START_DATE) GT EXTRACT(DAY FROM RS3RS_END_DATE) THEN

EXTRACT(DAY FROM RS3RS_END_DATE - EXTRACT(DAY FROM RS3RS_END_DATE) -

(EXTRACT(DAY FROM RS3RS_START_DATE) - EXTRACT(DAY FROM RS3RS_END_DATE)))

ELSE                   EXTRACT(DAY FROM RS3RS_END_DATE) - EXTRACT(DAY FROM RS3RS_START_DATE)

END

END AS GAP_D