Add years and move to last day

Question

Certain event needs to happen before the end of the year when it's 10 years since the previous event. I'm issuing a query like this to calculate the deadline:

SELECT
    :previous_date AS previous_date,
    ADD_MONTHS(
        TO_DATE(
            EXTRACT(
                YEAR FROM TO_DATE(:previous_date, 'YYYY-MM-DD HH24-MI-SS')
            ) || '-12-31 23-59-59', 'YYYY-MM-DD HH24-MI-SS'
        ), 12*10
    ) AS deadline
FROM DUAL

E.g.:

PREVIOUS_DATE DEADLINE                  
------------- ------------------------- 
2008-07-15    31/12/2018 23:59:59   

I works fine so far but... Am I missing some function that can make the query more readable?

Answer 1

You could use the trunc so that you get

SELECT add_months(trunc(sysdate, 'YYYY'), 12*11) - (1/86400) from dual

The (1/1440) takes a second off the 11 years, to give you 23:59:59

Answer 2

That's not really an answer but if the previous_date parameter was a date and not a string and you treated your deadline as exclusive (date < deadline rather than date <= deadline), your select would look like:

SELECT 
    :previous_date AS previous_date, 
    ADD_MONTHS( previous_date, 10 * 12) AS deadline 
FROM DUAL