CODEWITHSUNDEEP
oracle-databaseplsqlprocedureto-date
Shaun Kinnair
Shaun Kinnair

Reputation: 535

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.

Upvotes: 0

Views: 2444

Answers (2)

Steven Brown
Steven Brown

Reputation: 385

Another option is

select
dateadd(day, 1, (last_day(dateadd(quarter, -2, current_timestamp()), quarter))) as last_qtr_start,
last_day(dateadd(quarter, -1, current_timestamp()), quarter) as last_qtr_end
from dual;

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59642

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

Upvotes: 1

Related Questions