Oracle SQL: Dynamic timeframe calculation

1
votes

Greetings all knowing Stack.

I am in a bit of a pickle, and I am hoping for some friendly assistance form the hive mind.

I need to write a query that returns the difference in days between a registration date (stored in a table column) and the first day of the last September.

For example; assuming the query was being run today (24-10-2016) for a record with a registration date of 14-07-2010, I would want the script to return the difference in days between 14-07-2010 and 01-09-2016

However had I run the same query before the end of last August, for example on 12-08-2016, I would want the script to return the difference in days between 14-07-2010 and 01-09-2015.

I'm fine with the process of calculating differences between dates, it's just the process of getting the query to return the 'first day of the last September' into the calculation that is tripping me up!

Any input provided would be much appreciated.

Thankyou =)

2
sqloracledate

2 Answers

5
votes

Try this approach:

add four months to the current date

truncate this date to the first of year

subtract four months again

Add_Months(Trunc(Add_Months(SYSDATE, 4), 'year'), -4)
0
votes

Hope this might help.

WITH T AS (SELECT TO_DATE('14-07-2010','DD-MM-YYYY') REG_DATE,
                  SYSDATE EXEC_DATE
           FROM DUAL)
SELECT CASE WHEN TO_CHAR(EXEC_DATE,'MM') >= 9 
               THEN ADD_MONTHS(TRUNC(EXEC_DATE,'YEAR'),8) 
            ELSE ADD_MONTHS(TRUNC(ADD_MONTHS(EXEC_DATE,-12),'YEAR'),8)  
       END 
       - REG_DATE AS DIFF
FROM T;