CODEWITHSUNDEEP
oracle-databaseplsqldate-arithmetic
user3722544
user3722544

Reputation: 147

Find First and Last day of quarter using Function in Oracle

CREATE OR REPLACE FUNCTION dtqrtr(generationDate DATE)
RETURN DATE
IS
    dquery DATE;
BEGIN
    dquery := TRUNC(TO_DATE (generationDate),'YEAR');
    dquery := TRUNC(TO_DATE ( generationDate),'Q');
    dbms_output.put_line ('First_Quarter : ' || dquery || '   '||'Last_Quarter : ' || add_months(dquery -1,3));
END dquery;

END dtqrtr;

SELECT dtqrtr('20-JAN-2015')
FROM DUAL;

I want the year and first and last day of quarter. But I receive the error message:

ORA-06575: Package or function DTQRTR is in an invalid state

Upvotes: 0

Views: 491

Answers (3)

APC
APC

Reputation: 146239

You want two values: the first day of the quarter and the last day of the quarter. This is a poor fit for a function, which can only return a single value. However, both values are easily derivable with built-in SQL functions:

with cte as ( select date '2015-01-20' dt from dual )
select trunc(dt, 'Q') as qtr_st
       , last_day(add_months(trunc(dt, 'Q'),2)) as qtr_end
from cte
/

You might consider wrapping those three calls for QTR_END in a user-defined function, but it's probably only worthwhile if you're going to do this call a lot.

Upvotes: 0

cagcowboy
cagcowboy

Reputation: 30848

This should work...

CREATE OR REPLACE FUNCTION dtqrtr(generationDate DATE)
RETURN DATE
IS
    dquery DATE;
BEGIN
    dquery := TRUNC(TO_DATE (generationDate),'YEAR');
    dquery := TRUNC(TO_DATE ( generationDate),'Q');
    dbms_output.put_line ('First_Quarter : ' || dquery || '   '||'Last_Quarter : ' || add_months(dquery -1,3));
    RETURN dquery;
END dtqrtr;
/

SELECT dtqrtr('20-JAN-2015')
FROM DUAL;

Upvotes: 1

Sylvain Leroux
Sylvain Leroux

Reputation: 52000

ORA-06575 means that your function was compiled with errors. Very probably there is some syntax error in your function code. From SQL*Plus (and some other tools) you can display the error by using:

SHOW ERROR;

or

SHOW ERROR FUNCTION dtqrtr;

Here, my guess is you have too many END statements... and, as noticed by Ben in a comment bellow, a function has to return a value. Finally, it is rather strange too that you put something into dquery in the first line of the body, to overwrite it the next line.

Upvotes: 1

Related Questions