Solved

Create Month List to the two dates

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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…
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.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

867 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now