FORECAST FUTURE DATE 1

[dellguy]

I need to create a SELECT statement that provides a list of month-end dates from a particular start date to end date.Any ideas? Any help would be greatly appreciated.

 

[SantaMufasa]

DellGuy,

There are many, many ways to resolve your need. Following is one of those many ways. I saved the code to a script named "TT_600.sql". (You cannot simply copy and paste the code to the SQL*Plus prompt -- You must save the code to a script and run the script since the code contains a SQL*Plus "ACCEPT" statement.):

accept dt_beg prompt "Enter the start date (DD-MON-YYYY)    : "
accept dt_end prompt "Enter the   end date (DD-MON-YYYY)    : "
select Month_End
  from (select last_day(add_months(trunc(to_date('&dt_beg','DD-MON-YYYY'),'MM')
                                  ,(rownum-1))) Month_End
          from all_tab_columns
         where rownum <= months_between(to_date('&dt_end','DD-MON-YYYY')
                                       ,to_date('&dt_beg','DD-MON-YYYY')
                                       )+1
       )
 where Month_end <= last_day(to_date('&dt_end','DD-MON-YYYY'))
/

Here is a sample execution of my TT_600 script:

SQL> @tt_600
Enter the start date (DD-MON-YYYY): 15-NOV-2010
Enter the   end date (DD-MON-YYYY): 16-APR-2011

MONTH_END
---------------
30-NOV-10
31-DEC-10
31-JAN-11
28-FEB-11
31-MAR-11
30-APR-11

If you need/want an explanation of the purpose/functionality of any of the code, please post your request for clarification back to this thread.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[dellguy]

Thanks for the reply Mufasa. Appreciate it.

)

 

[KenCunningham]

Dellguy, if you appreciate it so much perhaps you could show it with the customary 'purple star' for Dave?

The internet - allowing those who don't know what they're talking about to have their say.