Post reply

To get month Names between two dates

  • September 20, 2011 at 3:13 am

    #248163

    hi,

    How to get the month names when i pass startdate and enddate . I need all the month names from startdate to end date .

    Ex:(20-09-2011 ,21-12-2011)--> september,october,november and december

  • September 20, 2011 at 3:41 am

    #1383686

    Homework question?

    How's this?

    DECLARE @startdate DATE = '2011-09-20', @endate DATE = '2011-12-21'

    SELECT a.months

    FROM (VALUES(1,'January'),(2,'February'),(3,'March'),(4,'April'),(5,'May'),(6,'June'),(7,'July'),

    (8,'August'),(9,'September'),(10,'October'),(11,'November'),(12,'December')) a(number,months)

    WHERE a.number >= DATEPART(month,@startdate) AND a.number <= DATEPART(month,@endate)

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • September 20, 2011 at 3:52 am

    #1383689

    Thanks fr reply

    declare @date1 datetime

    declare @date2 datetime

    set @date1=GETDATE()

    set @date2='2011-12-25'

    ;with cte as (

    select datename(month,@date1) as [Month_Name],@date1 as dat

    union all

    select datename(month,DateAdd(Month,1,dat)),DateAdd(Month,1,dat) from cte

    where DateAdd(Month,1,dat) < @date2)

    select [Month_Name] from CTE

    this gives correct format if months difference is 20 also it gives correct

  • September 20, 2011 at 4:06 am

    #1383695

    nhimabindhu (9/20/2011)

    Thanks fr reply

    declare @date1 datetime

    declare @date2 datetime

    set @date1=GETDATE()

    set @date2='2011-12-25'

    ;with cte as (

    select datename(month,@date1) as [Month_Name],@date1 as dat

    union all

    select datename(month,DateAdd(Month,1,dat)),DateAdd(Month,1,dat) from cte

    where DateAdd(Month,1,dat) < @date2)

    select [Month_Name] from CTE

    this gives correct format if months difference is 20 also it gives correct

    Doing it with a recursive CTE in that way will cause you problems. If you need to be able to do it for a big date range, look into a tally table.

    e.g.

    Try your CTE with the start date of 2000-12-20 and the end of GETDATE: -

    DECLARE @date1 DATETIME

    DECLARE @date2 DATETIME

    SET @date1 = '2000-12-20'

    SET @date2 = GETDATE();

    WITH cte

    AS (

    SELECT datename(month, @date1) AS [Month_Name], @date1 AS dat

    UNION ALL

    SELECT datename(month, DateAdd(Month, 1, dat)), DateAdd(Month, 1, dat)

    FROM cte

    WHERE DateAdd(Month, 1, dat) < @date2

    )

    SELECT [Month_Name]

    FROM CTE

    Recursion error on the CTE.

    Now, try that again with a tally table: -

    DECLARE @startdate DATE = '2000-12-20', @endate DATE = GETDATE()

    --Tally table on the fly, this should be an actual table instead for performance

    ;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),

    t2 AS (SELECT 1 N FROM t1 x, t1 y),

    t3 AS (SELECT 1 N FROM t2 x, t2 y),

    t4 AS (SELECT 1 N FROM t3 x, t3 y),

    tally AS (SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,0) AS betweendata

    FROM t4 x, t4 y)

    --Actual query

    SELECT DATENAME(MONTH,betweendata) AS months

    FROM tally

    WHERE betweendata >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @startdate), 0)

    AND betweendata <= DATEADD(MONTH, DATEDIFF(MONTH, 0, @endate), 0)

    And we get the 130 months returned.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • September 20, 2011 at 4:20 am

    #1383699

    DECLARE @SDATE DATETIME

    DECLARE @EDATE DATETIME

    DECLARE @MLIST TABLE

    (MName VARCHAR(30))

    SET @SDATE = '2000-09-20'

    SET @EDATE = GETDATE()

    WHILE (@SDATE < @EDATE)

    BEGIN

    INSERT INTO @MLIST

    SELECT DATENAME(month,@SDATE)

    SET @SDATE = DATEADD(MONTH,1,@SDATE)

    END

    SELECT * FROM @MLIST

  • September 20, 2011 at 4:32 am

    #1383706

    nhimabindhu (9/20/2011)

    DECLARE @SDATE DATETIME

    DECLARE @EDATE DATETIME

    DECLARE @MLIST TABLE

    (MName VARCHAR(30))

    SET @SDATE = '2000-09-20'

    SET @EDATE = GETDATE()

    WHILE (@SDATE < @EDATE)

    BEGIN

    INSERT INTO @MLIST

    SELECT DATENAME(month,@SDATE)

    SET @SDATE = DATEADD(MONTH,1,@SDATE)

    END

    SELECT * FROM @MLIST

    Really? 😛

    WHILE loops are bad in SQL Server, pretty much always. There are exceptions, this isn't one.

    Lets use some really unrealistic dates to illustrate, so start date is 1901-01-01 and end date is 4500-01-01

    DECLARE @EDATE DATETIME, @SDATE DATETIME

    DECLARE @MLIST TABLE

    (MName VARCHAR(30))

    SET @SDATE = '1901-01-01'

    SET @EDATE = '4500-01-01'

    WHILE (@SDATE <= @EDATE)

    BEGIN

    INSERT INTO @MLIST

    SELECT DATENAME(month,@SDATE)

    SET @SDATE = DATEADD(MONTH,1,@SDATE)

    END

    SELECT * FROM @MLIST

    Just over 3 seconds on my box for your WHILE loop.

    How about the tally table?

    DECLARE @startdate DATE = '1901-01-01', @endate DATE = '4500-01-01'

    --Tally table on the fly, this should be an actual table instead for performance

    ;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),

    t2 AS (SELECT 1 N FROM t1 x, t1 y),

    t3 AS (SELECT 1 N FROM t2 x, t2 y),

    t4 AS (SELECT 1 N FROM t3 x, t3 y),

    tally AS (SELECT DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,0) AS betweendata

    FROM t4 x, t4 y)

    --Actual query

    SELECT DATENAME(MONTH,betweendata) AS months

    FROM tally

    WHERE betweendata >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @startdate), 0)

    AND betweendata <= DATEADD(MONTH, DATEDIFF(MONTH, 0, @endate), 0)

    0.3 seconds, so 10x faster.

    Lets try again with even more unrealistic dates, start date 1901-01-01 and end date of 7000-01-01.

    Tally table takes 0.5 seconds.

    WHILE loop takes 6 seconds.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • September 20, 2011 at 4:39 am

    #1383713

    option (maxrecursion 0)

    now Recursssion error wont occur if i write this code in that program

  • September 20, 2011 at 4:45 am

    #1383720

    nhimabindhu (9/20/2011)

    option (maxrecursion 0)

    now Recursssion error wont occur if i write this code in that program

    Yes, but it's still over 2x slower than the tally table solution I suggested.

    StartDate '1901-01-01', EndDate '4500-01-01'

    Tally Table 0.3 seconds

    CTE (with OPTION (maxrecursion 0)) 0.7 seconds

    WHILE loop 3 seconds

    StartDate '1901-01-01', EndDate '7000-01-01'

    Tally Table 0.5 seconds

    CTE (with OPTION (maxrecursion 0)) 1.5 seconds

    WHILE loop 6 seconds

    And bear in mind that these times are including generating the tally table. If I base the tally table query from a properly indexed already created tally table then the query gets even faster.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy