Solved

Create Month List to the two dates

Posted on 2014-01-04
5
435 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 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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

691 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