CODEWITHSUNDEEP
oracle-databasedate
TThomas
TThomas

Reputation: 1

Oracle SQL Need list of first and last day of months after a date

I am hoping for a more elegant query that generates a list of the first day of the month and the last day of the month for all months after a date such as 01/01/2013 from a read only database that has no date table. My solution works but it bothers me.

I apologize in advance for my brute force solution but here is a snippet what i am using today. 

select ((add_months(trunc(sysdate,'mm'),-1))) as FirstDOM, (last_day(add_months(trunc(sysdate,'mm'),-1))) as LASTDOM from dual union ALL 
select ((add_months(trunc(sysdate,'mm'),-2))), (last_day(add_months(trunc(sysdate,'mm'),-2))) from dual union ALL
select ((add_months(trunc(sysdate,'mm'),-3))), (last_day(add_months(trunc(sysdate,'mm'),-3))) from dual union ALL
select ((add_months(trunc(sysdate,'mm'),-4))), (last_day(add_months(trunc(sysdate,'mm'),-4))) from dual union ALL
select ((add_months(trunc(sysdate,'mm'),-5))), (last_day(add_months(trunc(sysdate,'mm'),-5))) from dual union ALL
select ((add_months(trunc(sysdate,'mm'),-6))), (last_day(add_months(trunc(sysdate,'mm'),-6))) from dual union ALL
select ((add_months(trunc(sysdate,'mm'),-7))), (last_day(add_months(trunc(sysdate,'mm'),-7))) from dual union ALL
select ((add_months(trunc(sysdate,'mm'),-8))), (last_day(add_months(trunc(sysdate,'mm'),-8))) from dual union ALL
select ((add_months(trunc(sysdate,'mm'),-9))), (last_day(add_months(trunc(sysdate,'mm'),-9))) from dual union ALL
select ((add_months(trunc(sysdate,'mm'),-10))), (last_day(add_months(trunc(sysdate,'mm'),-10))) from dual union ALL
select ((add_months(trunc(sysdate,'mm'),-11))), (last_day(add_months(trunc(sysdate,'mm'),-11))) from dual union ALL
select ((add_months(trunc(sysdate,'mm'),-12))), (last_day(add_months(trunc(sysdate,'mm'),-12))) from dual union ALL
select ((add_months(trunc(sysdate,'mm'),-13))), (last_day(add_months(trunc(sysdate,'mm'),-13))) from dual union ALL
select ((add_months(trunc(sysdate,'mm'),-14))), (last_day(add_months(trunc(sysdate,'mm'),-14))) from dual) fldom

but this actually continues for 60ish months.
thank you!

Upvotes: 0

Views: 584

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

A hierarchical query does that in a simple manner. Here's an example:

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> set ver off
SQL> with test (col) as
  2    (select date '2013-01-01' from dual),
  3  all_months as
  4    (select add_months(col, level - 1) mon
  5     from test
  6     connect by level <= &number_of_months
  7    )
  8  select trunc(mon, 'mm') first_day,
  9    last_day(mon) last_day
 10  from all_months
 11  order by 1;
Enter value for number_of_months: 15

FIRST_DAY  LAST_DAY
---------- ----------
01.01.2013 31.01.2013
01.02.2013 28.02.2013
01.03.2013 31.03.2013
01.04.2013 30.04.2013
01.05.2013 31.05.2013
01.06.2013 30.06.2013
01.07.2013 31.07.2013
01.08.2013 31.08.2013
01.09.2013 30.09.2013
01.10.2013 31.10.2013
01.11.2013 30.11.2013
01.12.2013 31.12.2013
01.01.2014 31.01.2014
01.02.2014 28.02.2014
01.03.2014 31.03.2014

15 rows selected.

SQL>

Just for an example, I chose to display 15 months. You'd use something else, either a fixed value (60 - the value you mentioned), or computed (e.g. using MONTHS_BETWEEN).

Upvotes: 1

Related Questions