How to determine the number of days in a month in SQL Server ?

How to determine the number of days in a month in SQL Server ?

Asked on December 22, 2018 in SQL Server.
Add Comment


  • 5 Answer(s)

    To get first day of the month , use this code:

    datediff(day, @date, dateadd(month, 1, @date))
    

    If this works with everydate of the month,

    datediff(day, dateadd(day, 1-day(@date), @date),
                  dateadd(month, 1, dateadd(day, 1-day(@date), @date)))
    
    Answered on December 22, 2018.
    Add Comment

    To get the last day of the month In SQL Server 2012 use EOMONTH  which lies in the Transact-SQL, after that use DAY function to get the number of days in month like this,

    DECLARE @ADate DATETIME
     
    SET @ADate = GETDATE()
     
    SELECT DAY(EOMONTH(@ADate)) AS DaysInMonth
    
    Answered on December 22, 2018.
    Add Comment

    Use the code given below, for deal with the Date.

    DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@DATE),0)))
    

    Make it as a inline code. For example,

    SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'1/31/2009'),0)))
    

    It will Return 31

    SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'2404-feb-15'),0)))
    

    It Returns 29

    SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'2011-12-22'),0)))
    

    It Returns 31

    Answered on December 22, 2018.
    Add Comment
    DECLARE @ADate DATETIME
    
    SET @ADate = GETDATE()
    
    SELECT DAY(EOMONTH(@ADate)) AS DaysInMonth
    Answered on January 14, 2019.
    Add Comment

    You do need to add a function, but it’s a simple one. I use this:

    CREATE FUNCTION [dbo].[ufn_GetDaysInMonth] ( @pDate    DATETIME )
    
    RETURNS INT
    AS
    BEGIN
    
        SET @pDate = CONVERT(VARCHAR(10), @pDate, 101)
        SET @pDate = @pDate - DAY(@pDate) + 1
    
        RETURN DATEDIFF(DD, @pDate, DATEADD(MM, 1, @pDate))
    END
    
    GO
    Answered on January 14, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.