Creating SQL Function to print out start and end date of previous quarter-CodePudding

I want to develop an SQL function that get the quarter date range based on the current date

Example

First Quater: Jan 1 to March 31 2nd Qtr: April to June 30 3rd Qtr: Jul to Sept 4th Qtr: Oct - Dec

So depending on the date I run the sql function. It outputs the date for the start and end of the previous quarter.

So if I run it today (18th April 2022)

It outputs, Jan 1 2022 and Mar 31 2022

Please help thanks

CodePudding user response：

So depending on the date I run the sql function. It outputs the date for the start and end of the previous quarter.

You do not need a function and can do it entirely in SQL:

SELECT TRUNC(ADD_MONTHS(SYSDATE, -3), 'Q') AS previous_quarter_start,
       TRUNC(SYSDATE, 'Q') - 1 AS previous_quarter_end
FROM   DUAL;

Which outputs:

PREVIOUS_QUARTER_START PREVIOUS_QUARTER_END

2022-01-01 00:00:00 2022-03-31 00:00:00

PREVIOUS_QUARTER_START	PREVIOUS_QUARTER_END
2022-01-01 00:00:00	2022-03-31 00:00:00

If you do want a function then you need to create a type so that function can return multiple values:

CREATE TYPE date_range AS OBJECT(
  start_date DATE,
  end_date   DATE
);

Then the function can be defined as:

CREATE FUNCTION previous_quarter(
  i_date DATE DEFAULT SYSDATE
) RETURN date_range
AS
BEGIN
  RETURN date_range(TRUNC(ADD_MONTHS(i_date, -3), 'Q'), TRUNC(i_date, 'Q') - 1);
END;
/

Then, in PL/SQL:

DECLARE
  prev_quarter DATE_RANGE := previous_quarter;
BEGIN
  DBMS_OUTPUT.PUT_LINE( prev_quarter.start_date || ' - ' || prev_quarter.end_date );
END;
/

Outputs:

2022-01-01 00:00:00 - 2022-03-31 00:00:00

Or, in SQL:

SELECT previous_quarter().start_date, previous_quarter().end_date FROM DUAL;

Outputs:

PREVIOUS_QUARTER().START_DATE PREVIOUS_QUARTER().END_DATE

2022-01-01 00:00:00 2022-03-31 00:00:00

PREVIOUS_QUARTER().START_DATE	PREVIOUS_QUARTER().END_DATE
2022-01-01 00:00:00	2022-03-31 00:00:00

db<>fiddle here

CodePudding user response：

Here's one option; read comments within code (this is Oracle syntax as you said you use Oracle SQL Developer):

SQL> create or replace function f_previous_quarter (par_datum in date)
  2    return varchar2
  3  is
  4    retval varchar2(30);
  5  begin
  6    with
  7      temp (datum) as
  8        -- 4 rows for 4 quarters in a year (related to PAR_DATUM)
  9        (select add_months(trunc(par_datum, 'yyyy'), level * 3)
 10         from dual
 11         connect by level <= 4
 12        ),
 13      quarters as
 14        -- start and end date for each quarter from the TEMP CTE
 15        (select trunc(add_months(datum, -6), 'mm') q_start,
 16                last_day(add_months(datum, -4)) q_end
 17         from temp
 18        )
 19      -- finally:
 20      select to_char(q_start, 'Mon dd yyyy', 'nls_date_language = english') || ' - '||
 21             to_char(q_end  , 'Mon dd yyyy', 'nls_date_language = english')
 22      into retval
 23      from quarters
 24      -- subtract 3 months as you need PREVIOUS quarter
 25      where add_months(par_datum, -3) between q_start and q_end;
 26
 27    return retval;
 28  end;
 29  /

Function created.

Testing:

SQL> select f_previous_quarter(date '2022-04-18') val from dual;

VAL
--------------------------------------------------------------------------------
Jan 01 2022 - Mar 31 2022

SQL> select f_previous_quarter(date '2022-02-13') val from dual;

VAL
--------------------------------------------------------------------------------
Oct 01 2021 - Dec 31 2021

SQL> select f_previous_quarter(date '1997-08-28') val from dual;

VAL
--------------------------------------------------------------------------------
Apr 01 1997 - Jun 30 1997

SQL>