CODEWITHSUNDEEP
oracle-databaseplsql
Spaghetti
Spaghetti

Reputation: 187

Month End Date Range where most recent day = system date

I am trying to generate a range of 'last day of the month' dates from a given start date (01/01/2019) to the system date (sysdate). I am able to generate the date range for this using the below query:

select last_day(add_months (trunc (to_date('01/01/2019','MM/DD/YYYY'), 'MM'), Level - 1))

Month   FROM Dual

CONNECT BY Level <= MONTHS_BETWEEN(sysdate, to_date('01/01/2019','MM/DD/YYYY')) + 1

order by month

I am trying to get the last record to equal the sysdate so I can calculate running balances - how can I go about adding this to the above query?

Example output of the solution:

+------------------------+
|         MONTH          |
+------------------------+
| 1/31/2019 12:00:00 AM  |
| 2/28/2019 12:00:00 AM  |
| 3/31/2019 12:00:00 AM  |
| 4/30/2019 12:00:00 AM  |
| 5/31/2019 12:00:00 AM  |
| 6/30/2019 12:00:00 AM  |
| 7/31/2019 12:00:00 AM  |
| 8/31/2019 12:00:00 AM  |
| 9/30/2019 12:00:00 AM  |
| 10/31/2019 12:00:00 AM |
| 11/30/2019 12:00:00 AM |
| 12/31/2019 12:00:00 AM |
| 1/31/2020 12:00:00 AM  |
| 2/25/2020 12:00:00 AM  |
+------------------------+

Upvotes: 0

Views: 53

Answers (2)

Belayer
Belayer

Reputation: 14886

When I first saw this I immediately thought a recursive CTE would be perfect. The problem I kept getting a type mismatch on the recursion that I could not resolve. It turned out Oracle 11g has a bug in recursive CTE with recurson with dates. This finally got me to updating at long last. So thanks for the question. Anyway for future viewers a recursive CTE does work. 

alter session set nls_date_format = 'yyyy-mm-dd';
with date_list (dte) as
   ( select last_day(date '2019-01-01') from dual   
     union all 
     select least(add_months(dte,1), trunc(sysdate))  
       from date_list
      where dte<trunc(sysdate)
   )  
select dte from date_list;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142743

If I understood you correctly, you'd want today's date in the last line. If so, use CASE (lines #13 - 19).

Lines #1 - 12 represent your current query.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> WITH your_data
  2       AS (    SELECT LAST_DAY (
  3                         ADD_MONTHS (
  4                            TRUNC (TO_DATE ('01/01/2019', 'MM/DD/YYYY'), 'MM'),
  5                            LEVEL - 1))
  6                         Month
  7                 FROM DUAL
  8           CONNECT BY LEVEL <=
  9                           MONTHS_BETWEEN (
 10                              SYSDATE,
 11                              TO_DATE ('01/01/2019', 'MM/DD/YYYY'))
 12                         + 1)
 13    SELECT CASE
 14              WHEN TRUNC (month, 'mm') = TRUNC (SYSDATE, 'mm')
 15              THEN
 16                 TRUNC (SYSDATE)
 17              ELSE
 18                 month
 19           END
 20              month
 21      FROM your_data
 22  ORDER BY month;

MONTH
-------------------
31.01.2019 00:00:00
28.02.2019 00:00:00
31.03.2019 00:00:00
30.04.2019 00:00:00
31.05.2019 00:00:00
30.06.2019 00:00:00
31.07.2019 00:00:00
31.08.2019 00:00:00
30.09.2019 00:00:00
31.10.2019 00:00:00
30.11.2019 00:00:00
31.12.2019 00:00:00
31.01.2020 00:00:00
18.02.2020 00:00:00

14 rows selected.

SQL>

Upvotes: 1

Related Questions