CODEWITHSUNDEEP
sqloracle-databasedateplsql
Jeremy Thompson
Jeremy Thompson

Reputation: 65732

Oracle End Of Month values for Date Range

Oracle has a last_day() function:

SELECT LAST_DAY(to_date('01/01/2016','DD/MM/YYYY')) from dual;

Results:

31/01/2016

How can I get a list of the EndOfMonths in a Date Range to use in a WHERE Clause, currently I'm using a big string and it is not intuitive or future proof eg:

SELECT * FROM Balances
WHERE TheDate IN
('31/Jan/2016','29/Feb/2016', '31/Mar/2016','30/Apr/2016', '31/May/2016','30/Jun/2016', '31/Jul/2016','31/Aug/2016',
'30/Sep/2016', '31/Oct/2016','30/Nov/2016')

I'd prefer to use a function to plug in Start and End Dates.

Upvotes: 1

Views: 8325

Answers (2)

OldProgrammer
OldProgrammer

Reputation: 12179

Try something like this to generate a last day of month range:

SELECT LAST_DAY(add_months(to_date('01/01/2016','DD/MM/YYYY'), level) )
from dual
CONNECT BY LEVEL <= 10

Change 10 to # months you need. This assumes "TheDate" is a proper oracle DATE type. Wrap with to_char if needed to format as string.

With @JeremyThompsons Months_Between suggestion:

SELECT LAST_DAY(add_months(to_date('01-01-1991','DD/MM/YYYY'), level) )
from dual
CONNECT BY LEVEL <=
(SELECT MONTHS_BETWEEN 
   (TO_DATE('02-02-1999','MM-DD-YYYY'),
    TO_DATE('01-01-1991','MM-DD-YYYY') ) "Months"
    FROM dual);

The final query with @mathguys function order suggestion:

SELECT * FROM Balances b
WHERE TheDate IN
(SELECT add_months(LAST_DAY(to_date('28/Feb/2015','DD-MM-YYYY')), level) 
from dual
CONNECT BY LEVEL <=
(SELECT MONTHS_BETWEEN 
   (TO_DATE('30/Nov/2016','DD-MM-YYYY'),
    TO_DATE('28/Feb/2015','DD-MM-YYYY') ) "Months"
    FROM dual));

And one more of @mathguys optimizations, no need for scalar subquery:

SELECT * FROM Balances b
WHERE TheDate IN
(SELECT add_months(LAST_DAY(to_date('28/Feb/2015','DD-MM-YYYY')),level)
from dual
CONNECT BY LEVEL <= MONTHS_BETWEEN 
   (TO_DATE('30/Nov/2016','DD-MM-YYYY'),
    TO_DATE('28/Feb/2015','DD-MM-YYYY')) );

Upvotes: 1

bprasanna
bprasanna

Reputation: 2453

You can create a function like the following which will return the end of months for all the months:

create or replace function getEndOfMonths return varchar2 as
    cnt number;
    current_year varchar2(5);
    end_of_months varchar2(180) := '';
    end_of_month varchar2(13);
begin
    select to_char(sysdate,'YYYY') into current_year from dual;
    for cnt in 1..12 loop
        SELECT LAST_DAY(to_date('01/'||cnt||'/'||current_year,'DD/MM/YYYY')) into end_of_month from dual;
        end_of_months := end_of_months||','||end_of_month;
    end loop;
    return substr(end_of_months,2);
end;
/

Update: Following is based on month range:

create or replace function getEndOfMonths(start_month number, end_month number) return varchar2 as
    cnt number;
    current_year varchar2(5);
    end_of_months varchar2(180) := '';
    end_of_month varchar2(13);
begin
    select to_char(sysdate,'YYYY') into current_year from dual;
    for cnt in start_month..end_month loop
        SELECT LAST_DAY(to_date('01/'||cnt||'/'||current_year,'DD/MM/YYYY')) into end_of_month from dual;
        end_of_months := end_of_months||','||end_of_month;
    end loop;
    return substr(end_of_months,2);
end;
/

Upvotes: 0

Related Questions