RDG RDG - 1 month ago 19
SQL Question

Oracle SQL: Dynamic timeframe calculation

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 =)

Answer

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)