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

Question

Is there a way to get the number of days in a month in SQL Server, if we input the month number or month name, or even a date?

Answer 1

You can use:

select day(eomonth ('2018-02-01')) as NoOfDays

and the result will be:

NoOfDays
-----------
28

Answer 2

If you have a date, then simply do:

select day(eomonth(date))

If you have a month number, then:

select day(eomonth(datefromparts(2020, month_number, 1)))

Answer 3

If you have a date and are on 2012 or later :

SELECT day(eomonth(yourdate))

Month name / number is automatically prone to an error when dealing with February - do you consider it 28 or 29, which year are you referring to when making that calculation etc.

Answer 4

In case you are using sql-server 2008 or earlier:

Date as input

DECLARE @date DATETIME = getdate()

SELECT day(dateadd(m, datediff(m, -1, @date), -1))

Month and year as input

DECLARE @year  INT = 2018
DECLARE @month INT = 2

SELECT day(dateadd(m, @month + datediff(m, 0, cast(@year as char(4))), -1))

Answer 5

try using

SELECT day(eomonth(yourdate))