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?

0 votes

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?

0 votes

```
--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...

0 votes

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)
```

0 votes

here's another one...

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

0 votes

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)))
```

0 votes

I know this question is old but I thought I would share what I'm using.

```
DECLARE @date date = '2011-12-22'
/* FindFirstDayOfMonth - Find the first date of any month */
-- Replace the day part with -01
DECLARE @firstDayOfMonth date = CAST( CAST(YEAR(@date) AS varchar(4)) + '-' +
CAST(MONTH(@date) AS varchar(2)) + '-01' AS date)
SELECT @firstDayOfMonth
```

and

```
DECLARE @date date = '2011-12-22'
/* FindLastDayOfMonth - Find what is the last day of a month - Leap year is handled by DATEADD */
-- Get the first day of next month and remove a day from it using DATEADD
DECLARE @lastDayOfMonth date = CAST( DATEADD(dd, -1, DATEADD(mm, 1, FindFirstDayOfMonth(@date))) AS date)
SELECT @lastDayOfMonth
```

Those could be combine to create a single function to retrieve the number of days in a month if needed.

0 votes

```
SELECT DAY(SUBDATE(ADDDATE(CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-1'), INTERVAL 1 MONTH), INTERVAL 1 DAY))
```

Nice 'n' Simple and does not require creating any functions

0 votes

```
SELECT Datediff(day,
(Convert(DateTime,Convert(varchar(2),Month(getdate()))+'/01/'+Convert(varchar(4),Year(getdate())))),
(Convert(DateTime,Convert(varchar(2),Month(getdate())+1)+'/01/'+Convert(varchar(4),Year(getdate()))))) as [No.of Days in a Month]
```

0 votes

**Solution 1: Find the number of days in whatever month we're currently in**

```
DECLARE @dt datetime
SET @dt = getdate()
SELECT @dt AS [DateTime],
DAY(DATEADD(mm, DATEDIFF(mm, -1, @dt), -1)) AS [Days in Month]
```

**Solution 2: Find the number of days in a given month-year combo**

```
DECLARE @y int, @m int
SET @y = 2012
SET @m = 2
SELECT @y AS [Year],
@m AS [Month],
DATEDIFF(DAY,
DATEADD(DAY, 0, DATEADD(m, ((@y - 1900) * 12) + @m - 1, 0)),
DATEADD(DAY, 0, DATEADD(m, ((@y - 1900) * 12) + @m, 0))
) AS [Days in Month]
```

0 votes

In SQL Server 2012 you can use EOMONTH (Transact-SQL) to get the last day of the month and then you can use DAY (Transact-SQL) to get the number of days in the month.

```
DECLARE @ADate DATETIME
SET @ADate = GETDATE()
SELECT DAY(EOMONTH(@ADate)) AS DaysInMonth
```

...