By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT TRUNC(ADD_MONTHS(SYSDATE, -3), 'Q') AS previous_quarter_start,
TRUNC(SYSDATE, 'Q') - 1 AS previous_quarter_end
FROM DUAL;
| PREVIOUS_QUARTER_START | PREVIOUS_QUARTER_END |
|---|---|
| 2022-01-01 00:00:00 | 2022-03-31 00:00:00 |
CREATE TYPE date_range AS OBJECT(
start_date DATE,
end_date DATE
);
CREATE FUNCTION previous_quarter RETURN date_range
AS
BEGIN
RETURN date_range(TRUNC(ADD_MONTHS(SYSDATE, -3), 'Q'), TRUNC(SYSDATE, 'Q') - 1);
END;
/
DECLARE
prev_quarter DATE_RANGE := previous_quarter;
BEGIN
DBMS_OUTPUT.PUT_LINE( prev_quarter.start_date || ' - ' || prev_quarter.end_date );
END;
/
1 rows affected
dbms_output:
2022-01-01 00:00:00 - 2022-03-31 00:00:00