Date duration issue

Database
N/A

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

1 REPLY
Senior Apprentice

Re: Date duration issue

There was a similar question a few days ago (same company?)

Data Caclulation (Number of Full Months and remaining Days between two dates)

Try to add the number of months to the start date or substract it from the end date and then simply substract end - start. But you will always find some problematic dates, there's a reason why there's no "official" calculation for that.

You might also look at the MONTHS_BETWEEN function in TD14 (based on Oracle), but this also has some flaws.

I would still recommend weeks :-)