?
Solved

Create Month List to the two dates

Posted on 2014-01-04
5
Medium Priority
?
436 Views
Last Modified: 2014-01-07
Dear Experts,
I have a starting and a ending date, I need to create Month list of them with Month No.
Please help


Starting Date 04/01/2013
Ending Date 03/31/2014

A List Need something like this


Month No.                 Month
4                                Apr-2013
5                                May-2013
1                                Jan-2014

Please help
Rgds.
Mehram
0
Comment
Question by:Mehram
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 2000 total points
ID: 39755940
You can try the following code:
DECLARE @StartingDate DATE = '04/01/2013' ,
    @EndingDate DATE = '03/31/2014'
	
DECLARE @Table TABLE
    (
      MonthNumber TINYINT ,
      MunthName CHAR(8)
    )

DECLARE @Date DATE = @StartingDate
WHILE @Date < @EndingDate 
    BEGIN
        INSERT  INTO @Table
                ( MonthNumber ,
                  MunthName 
                )
        VALUES  ( DATEPART(m, @Date) ,
				  SUBSTRING(DATENAME(m, @Date), 1, 3) + '-' + CAST(DATEPART(yy, @Date) AS CHAR(4))
                )
        SET @Date = DATEADD(m, 1, @Date)
    
    END
    

SELECT  *
FROM    @Table

Open in new window

0
 

Author Comment

by:Mehram
ID: 39756023
Sir,
Is there any way to get result directly through sp (sDate & eDate)

Without inserting value in table
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 39756043
you can create a sp for it:
CREATE PROCEDURE GetMonths
    (
      @StartingDate DATE ,
      @EndingDate DATE
    )
AS 
    BEGIN
        SET NOCOUNT ON
        DECLARE @Table TABLE
            (
              MonthNumber TINYINT ,
              [MonthName] CHAR(8)
            )

        DECLARE @Date DATE = @StartingDate
        WHILE @Date < @EndingDate 
            BEGIN
                INSERT  INTO @Table
                        ( MonthNumber ,
                          [MonthName]
                        )
                VALUES  ( DATEPART(m, @Date) ,
                          SUBSTRING(DATENAME(m, @Date), 1, 3) + '-'
                          + CAST(DATEPART(yy, @Date) AS CHAR(4))
                        )
                SET @Date = DATEADD(m, 1, @Date)
    
            END
    

        SELECT  *
        FROM    @Table
        SET NOCOUNT OFF
    END
	

Open in new window


and execute sp:
EXEC [dbo].[GetMonths] @StartingDate = '04/01/2013', @EndingDate = '03/31/2014'

Open in new window

0
 

Author Comment

by:Mehram
ID: 39756072
Thanks Sir
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39761455
BTW: alternatively you could use a "recursive CTE" instead of a table.

just as an example:
declare @BeginsAt as datetime = '20120111'
declare @numMonths as int = 36

set @BeginsAt  = dateadd(month,datediff(month,0,@BeginsAt),0) -- force to 1st of Month

;with MonthRange (id, StartAt, StopAt)
as (
    select 1 as id, @BeginsAt, dateadd(month,1,@BeginsAt)
    union all
    select (id + 1) , dateadd(month,1,StartAt) , dateadd(month,1,StopAt)
    from MonthRange
    where (id + 1) <= @numMonths
    )
select
*
from MonthRange

Open in new window

0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question