Home » SQL & PL/SQL » SQL & PL/SQL » How to Calculate the number of periods for the given Start date and End date
How to Calculate the number of periods for the given Start date and End date [message #424317] Thu, 01 October 2009 09:37 Go to next message
nav78
Messages: 2
Registered: October 2009
Location: Boston
Junior Member
Hi,

I need a solution for the below mentioned criteria.

Start Date : 06/12/2009
End Date : 09/30/2009

So based on the Start Date(06/12/2009) and End Date(09/30/2009), I need to insert the rows as mentioned below in PL/SQL block.

like

Fund Start Date End Date
------- -------------- ------------
abc 06/01/2009 06/30/2009
abc 07/01/2009 07/31/2009
abc 08/01/2009 08/31/2009
abc 09/01/2009 09/30/2009


Please advice me the solution....
Re: How to Calculate the number of periods for the given Start date and End date [message #424319 is a reply to message #424317] Thu, 01 October 2009 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
>I need to insert the rows as mentioned below in PL/SQL block.
I need new glasses.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
I need to insert the rows as mentioned below in PL/SQL block [message #424322 is a reply to message #424319] Thu, 01 October 2009 09:50 Go to previous messageGo to next message
nav78
Messages: 2
Registered: October 2009
Location: Boston
Junior Member
Sure ..I will do the same
Re: How to Calculate the number of periods for the given Start date and End date [message #424329 is a reply to message #424317] Thu, 01 October 2009 10:23 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
There are several methods.

The most common method is to create a table that contains all the periods you might ever want, then use your dates to join to the table. This method is most flexible because it allows you to create periods that do not have to be "equal". Periods can be of any duration and it still works fine.

The second most common method is to create a calculation that returns the periods you want. More often than not this requires that the periods in question have "regularity" to them for example they have easily definable start/stop points like first day of the month/last day of the month. You example suggests this will work for you as well. Consult the DATE functions of Oracle for LAST_DAY, TRUNC, TO_DATE etc. The first day of the month for example would be LAST_DAY(<somedate>)-1.

Also, learn how to format your code when posting.

Kevin
Previous Topic: Accent insensitive comparing problem
Next Topic: VARCHAR2 problem
Goto Forum:
  


Current Time: Mon Sep 26 19:39:58 CDT 2016

Total time taken to generate the page: 0.08963 seconds