oracle-databaseplsqlprocedureto-date

Oracle SQL date getting first day of last quarter and last day of last quarter

I've been asked to provide an Oracle PL/SQL solution if a file is loaded into the system for example between the dates of 1st Jan 2017 - 31st March 2017 I should created two dates from the last quarter a loaded from date of 1st Oct 2016 and loaded to date of 31st Dec 2016. This should be future prove meaning it should work for future years, so if a file is loaded into the system lets say 21st August 2019, it should have a from date of 1st April 2019 and a to date of 30th June 2019.

enter image description here

This should be a PL/SQL solution most probably a procedure returning two dates to the main program and the to and from date returned should be in the format of DD/MM/YYYY.

Thanks in advance.

Solution

  • What about this solution?

    SELECT 
    TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -3), 'DD/MM/YYYY') AS output_from_date,
    TO_CHAR(TRUNC(SYSDATE, 'Q')-1, 'DD/MM/YYYY') AS output_to_date
FROM dual;

OUTPUT_FROM_DATE    OUTPUT_TO_DATE
01/04/2017          30/06/2017