How to get number of days in a month in SQL Server?

Sometimes we need to get the total number of days in month for given date, there is no build in function which can help us to directly use and get our result, so we need to write a small SQL statement to get the total number of days for a particular date.

I found on some blog sites to use this but it will not work for every date

DECLARE @date DATETIME
SET @date = '05/17/2020'
SELECT DATEDIFF(Day, @date, DATEADD(Month, 1, @date))

Try to use date '1/31/2013' or '3/31/2013' or '5/31/2013'

For 1/31 it will give 28 and for '3/31/2013' and '5/31/2013' it will return 30 which is wrong, so we will use the correct one like this

DECLARE @date DATETIME
SET @date = '05/17/2020'
SELECT DATEDIFF(Day, DATEADD(day, 1 - Day(@date), @date),
              DATEADD(Month, 1, DATEADD(Day, 1 - Day(@date), @date)))

Try it with above dates or any other date you want to check and it will always give correct result.

In SQL Server 2012 a new datetime function is introduced (actually 7 new datetime fucntions introduced) named EOMONTH which return last date of month so we can also use this to get the number of days in a month

Let's see first EOMONTH

SELECT EOMONTH(GETDATE()) LastDayofMonth

Result: 2013-05-31 00:00:00.000, so simply we can get days from this result so use like this

DECLARE @date DATETIME
SET @date = '1/31/2013'
SELECT DAY(EOMONTH(@date)) AS DaysInMonth