Solved

Create Month List to the two dates

Posted on 2014-01-04
5
434 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 500 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 48

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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