0

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.

Shaun Kinnair
  • 495
  • 2
  • 10
  • 27

2 Answers2

1

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
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
0

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;
Steven Brown
  • 365
  • 3
  • 8