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

sql sql-server datetime date user-defined-functions
Even Mien picture Even Mien · Mar 27, 2009 · Viewed 242.9k times · Source

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

Mikael Eriksson picture Mikael Eriksson · Feb 2, 2013

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

Related questions

How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
Best approach to remove time part of datetime in SQL Server

Which method provides the best performance when removing the time portion from a datetime field in SQL Server? a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) or b) select cast(convert(char(11), getdate(), 113) as datetime) The second method does send a …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More