[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Create Month List to the two dates

Posted on 2014-01-04
5
Medium Priority
?
437 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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…
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…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

649 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