none
month names and month days between two days

    Question

  • Hi gurus,

    i want to display month names and month days between to dates

    Ex:-if i give two dates like 12-25-2011 and 03-26-2012 then i have requirement like following

             MonthName             Days

             December                 06

            january                      31

            February                  29

            March                        26

    i wrote code as


    /

    ****** Object:  UserDefinedFunction [dbo].[GetMonthsNames]    Script Date: 12/23/2011 10:50:00 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER FUNCTION [dbo].[GetMonthsNames](@StartDate DATETIME, @EndDate DATETIME)
    
    RETURNS @MonthList TABLE(MonthValue VARCHAR(15) NOT NULL,NumberofDays int not null)
    AS
    BEGIN
        --Variable used to hold each new date value
        DECLARE @DateValue DATETIME
    
        --Start with the starting date in the range
        SET @DateValue=@StartDate
    
        --Load output table with the month part of each new date
        WHILE @DateValue <= @EndDate
        BEGIN
            INSERT INTO @MonthList(MonthValue,NumberofDays)
            SELECT DATENAME(mm,@DateValue),
            datediff(dd,@DateValue,DATEADD(dd,-DAY(DATEADD(m,1,@DateValue)),DATEADD(m,1,@DateValue)))
            
             
            --Move to the next month           
            SET @DateValue=DATEADD(mm,1,@DateValue)
        END
       
        --Return results
        RETURN
    END
    


    but it returns

    MonthName             Days

             December                 06

            january                      06

            February                  06

            March                        06

    if any one know please help me

    Thanks in Advance


    srikanth
    Friday, December 23, 2011 5:22 AM

Answers

  • Check this:

    declare @startDate datetime,
        @endDate datetime
    
    set @startDate = '11/15/2011'
    set @endDate = '03/15/2012'
    
    ; with cte as (
        select DATENAME(mm, @startDate) 'mon', DATEPART(d,@startDate) 'dd', @startDate 'new_date'
        UNION ALL
        select DATENAME(mm, new_date) 'mon', DATEPART(d,@startDate) 'dd', DATEADD(d,1,new_date) 'new_date'
        from cte
        WHERE new_date < @endDate
        )
    select mon, count(dd)
    from cte
    group by mon
    OPTION (MAXRECURSION 1000)
    
    


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    • Marked as answer by sunny0817 Friday, December 23, 2011 7:09 AM
    Friday, December 23, 2011 5:46 AM
  • This could be easily done with a calendar table.

    declare @startdate date='12-25-2011'
    declare @Enddate date='03-26-2012'

    select MONTHname,COUNT(D) as cnt from dbo.Calendar where dt > @startdate and dt<= @Enddate
    group by MONTHname

    Have a look:http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html


    Thanks and regards, Rishabh , Microsoft Community Contributor
    • Edited by Rishabh K Friday, December 23, 2011 5:49 AM
    • Proposed as answer by Naomi NModerator Friday, December 23, 2011 6:00 AM
    • Marked as answer by sunny0817 Friday, December 23, 2011 9:32 AM
    Friday, December 23, 2011 5:49 AM
  • Please try the below tsql -

    DECLARE @StartDate AS DATETIME = '20111225'
    DECLARE @EndDate AS DATETIME = '20120326'
    
    ;WITH Dates AS (
    SELECT @StartDate StartDate, DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,(DATEPART(DAY,@StartDate) * -1) + 1, @StartDate))) EndDate
    UNION ALL
    SELECT DATEADD(DAY,1,EndDate) StartDate,DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1,EndDate))) EndDate FROM Dates WHERE YEAR(EndDate) < YEAR(@EndDate) OR (YEAR(EndDate) = YEAR(@EndDate) AND MONTH(EndDate) <= MONTH(@EndDate))
    ),ValidDates AS (
    SELECT
      StartDate, 
      CASE WHEN YEAR(EndDate) = YEAR(@EndDate) AND MONTH(EndDate) = MONTH(@EndDate) THEN @EndDate ELSE EndDate END EndDate
    FROM 
      Dates
    WHERE
      CASE WHEN YEAR(EndDate) = YEAR(@EndDate) AND MONTH(EndDate) = MONTH(@EndDate) THEN @EndDate ELSE EndDate END BETWEEN @StartDate AND @EndDate
    )
    SELECT 
      DATENAME(MONTH,StartDate) [MonthName],
      DATEDIFF(DAY,StartDate,EndDate) + 1 [Days]
    FROM 
      ValidDates
      
    



    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    • Marked as answer by sunny0817 Friday, December 23, 2011 8:53 AM
    Friday, December 23, 2011 5:58 AM

All replies

  • Check this:

    declare @startDate datetime,
        @endDate datetime
    
    set @startDate = '11/15/2011'
    set @endDate = '03/15/2012'
    
    ; with cte as (
        select DATENAME(mm, @startDate) 'mon', DATEPART(d,@startDate) 'dd', @startDate 'new_date'
        UNION ALL
        select DATENAME(mm, new_date) 'mon', DATEPART(d,@startDate) 'dd', DATEADD(d,1,new_date) 'new_date'
        from cte
        WHERE new_date < @endDate
        )
    select mon, count(dd)
    from cte
    group by mon
    OPTION (MAXRECURSION 1000)
    
    


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    • Marked as answer by sunny0817 Friday, December 23, 2011 7:09 AM
    Friday, December 23, 2011 5:46 AM
  • This could be easily done with a calendar table.

    declare @startdate date='12-25-2011'
    declare @Enddate date='03-26-2012'

    select MONTHname,COUNT(D) as cnt from dbo.Calendar where dt > @startdate and dt<= @Enddate
    group by MONTHname

    Have a look:http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html


    Thanks and regards, Rishabh , Microsoft Community Contributor
    • Edited by Rishabh K Friday, December 23, 2011 5:49 AM
    • Proposed as answer by Naomi NModerator Friday, December 23, 2011 6:00 AM
    • Marked as answer by sunny0817 Friday, December 23, 2011 9:32 AM
    Friday, December 23, 2011 5:49 AM
  • Please try the below tsql -

    DECLARE @StartDate AS DATETIME = '20111225'
    DECLARE @EndDate AS DATETIME = '20120326'
    
    ;WITH Dates AS (
    SELECT @StartDate StartDate, DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,(DATEPART(DAY,@StartDate) * -1) + 1, @StartDate))) EndDate
    UNION ALL
    SELECT DATEADD(DAY,1,EndDate) StartDate,DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1,EndDate))) EndDate FROM Dates WHERE YEAR(EndDate) < YEAR(@EndDate) OR (YEAR(EndDate) = YEAR(@EndDate) AND MONTH(EndDate) <= MONTH(@EndDate))
    ),ValidDates AS (
    SELECT
      StartDate, 
      CASE WHEN YEAR(EndDate) = YEAR(@EndDate) AND MONTH(EndDate) = MONTH(@EndDate) THEN @EndDate ELSE EndDate END EndDate
    FROM 
      Dates
    WHERE
      CASE WHEN YEAR(EndDate) = YEAR(@EndDate) AND MONTH(EndDate) = MONTH(@EndDate) THEN @EndDate ELSE EndDate END BETWEEN @StartDate AND @EndDate
    )
    SELECT 
      DATENAME(MONTH,StartDate) [MonthName],
      DATEDIFF(DAY,StartDate,EndDate) + 1 [Days]
    FROM 
      ValidDates
      
    



    -Vinay Pugalia
    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Web : Inkey Solutions
    Blog : My Blog
    Email : Vinay Pugalia
    • Marked as answer by sunny0817 Friday, December 23, 2011 8:53 AM
    Friday, December 23, 2011 5:58 AM
  • thanks you guys you all are rocks
    srikanth
    Friday, December 23, 2011 7:09 AM
  • Skikanth,

    I would suggest you to go with Rishabh's idea of creating a separate Calendar table and use that in your query.

    That would be more performant and can be used in other areas too.


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Friday, December 23, 2011 7:29 AM
  • Check this:

     

    declare @startDate datetime,
        @endDate datetime
    
    set @startDate = '11/15/2011'
    set @endDate = '03/15/2012'
    
    ; with cte as (
        select DATENAME(mm, @startDate) 'mon', DATEPART(d,@startDate) 'dd', @startDate 'new_date'
        UNION ALL
        select DATENAME(mm, new_date) 'mon', DATEPART(d,@startDate) 'dd', DATEADD(d,1,new_date) 'new_date'
        from cte
        WHERE new_date < @endDate
        )
    select mon, count(dd)
    from cte
    group by mon
    OPTION (MAXRECURSION 1000)
    
    

     


    ~manoj | email: http://scr.im/m22g
    http://sqlwithmanoj.wordpress.com
    MCCA 2011

    Hi,

    Here is the perfect sequence:

    DECLARE @startDate DATETIME ,
        @endDate DATETIME
    
    SET @startDate = '11/15/2011'
    SET @endDate = '03/15/2012' ;
    WITH    cte
              AS ( SELECT   DATENAME(mm, @startDate) 'mon' ,
                            DATEPART(month, @startDate) 'mm' ,
                            DATEPART(year, @startDate) 'yy' ,
                            DATEPART(d, @startDate) 'dd' ,
                            @startDate 'new_date'
                   UNION ALL
                   SELECT   DATENAME(mm, new_date) 'mon' ,
                            DATEPART(month, new_date) 'mm' ,
                            DATEPART(year, new_date) 'yy' ,
                            DATEPART(d, new_date) 'dd' ,
                            DATEADD(d, 1, new_date) 'new_date'
                   FROM     cte
                   WHERE    new_date < @endDate
                 )
        SELECT  mon ,
                COUNT(dd)
        FROM    cte
        GROUP BY mon ,
                yy ,
                mm
        ORDER BY yy ,
                mm
    OPTION  ( MAXRECURSION 1000 )
    

    You will get Nov, Dec, Jan, Feb,

    I favor perfect answer. :) 

    Cheers,

    - John Backham

    Monday, December 26, 2011 11:36 AM
  • Hi,

    Here is the perfect sequence:

    DECLARE @startDate DATETIME ,
        @endDate DATETIME
    
    SET @startDate = '11/15/2011'
    SET @endDate = '03/15/2012' ;
    WITH    cte
              AS ( SELECT   DATENAME(mm, @startDate) 'mon' ,
                            DATEPART(month, @startDate) 'mm' ,
                            DATEPART(year, @startDate) 'yy' ,
                            DATEPART(d, @startDate) 'dd' ,
                            @startDate 'new_date'
                   UNION ALL
                   SELECT   DATENAME(mm, new_date) 'mon' ,
                            DATEPART(month, new_date) 'mm' ,
                            DATEPART(year, new_date) 'yy' ,
                            DATEPART(d, new_date) 'dd' ,
                            DATEADD(d, 1, new_date) 'new_date'
                   FROM     cte
                   WHERE    new_date < @endDate
                 )
        SELECT  mon ,
                COUNT(dd)
        FROM    cte
        GROUP BY mon ,
                yy ,
                mm
        ORDER BY yy ,
                mm
    OPTION  ( MAXRECURSION 1000 )
    

    You will get Nov, Dec, Jan, Feb,

    I favor perfect answer. :) 

    Cheers,

    - John Backham


    You may want to have a second look at your "perfect" answer.  It's impossible to have 17 days between the 15th of November and the end of November, which is the 30th, even if both dates are inclusive. ;-)

    --Jeff Moden


    • Edited by Jeff Moden Wednesday, July 27, 2016 3:07 AM
    Wednesday, July 27, 2016 3:06 AM