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

Question

I need to determine the number of days in a month for a given date in SQL Server.

Is there a built-in function? If not, what should I use as the user-defined function?

Answer 1

You can use the following with the first day of the specified month:

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

To make it work for every date:

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

Answer 2

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

Answer 3

I always find it funny when people asks questions that you get the answer two in the top result on google...

--Last Day of Previous Month
SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))

--Last Day of Current Month
SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))

--Last Day of Next Month
SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)))

Personally though, I would make a UDF for it if there is not a built in function...

Answer 4

I upvoted Mehrdad, but this works as well. :)

CREATE function dbo.IsLeapYear
(
    @TestYear int
)
RETURNS bit
AS
BEGIN
    declare @Result bit
    set @Result = 
    cast(
     case when ((@TestYear % 4 = 0) and (@testYear % 100 != 0)) or (@TestYear % 400 = 0)
     then 1
     else 0
     end
    as bit )
    return @Result
END
GO

CREATE FUNCTION dbo.GetDaysInMonth
(
    @TestDT datetime
)
RETURNS INT
AS
BEGIN

    DECLARE @Result int 
    DECLARE @MonthNo int

    Set @MonthNo = datepart(m,@TestDT)

    Set @Result = 
    case @MonthNo
     when  1 then 31
     when  2 then 
      case 
       when dbo.IsLeapYear(datepart(yyyy,@TestDT)) = 0
       then 28
       else 29
      end
     when  3 then 31
     when  4 then 30
     when  5 then 31
     when  6 then 30
     when  7 then 31
     when  8 then 31
     when  9 then 30 
     when 10 then 31
     when 11 then 30 
     when 12 then 31
    end

    RETURN @Result
END
GO

To Test

declare @testDT datetime;

set @testDT = '2404-feb-15';

select dbo.GetDaysInMonth(@testDT)

Answer 5

here's another one...

Select Day(DateAdd(day, -Day(DateAdd(month, 1, getdate())), 
                         DateAdd(month, 1, getdate())))