One thing which amazes me is the lack of some basic programmability in SQL especially around some core requirements of T-SQL. Dates is one of them.
To get the number of days in a month we get the first day in the month and the last. We then subtract from each other. One contains the 1st of this month and the other contains the 1st of next month. The minus includes one of the days but not the other.
The first day is found by taking todays day of the month, minusing it from 1 (-day of month + 1 ) and then adding it to today day of the month which equates to 1.
the last day of the month / 1st day of next month is found by adding 1 month onto the first day
declare @daysInMonth as int declare @firstMonth as dateTime declare @firstNextMonth as dateTime set @firstMonth = DATEADD (d, 1 - DAY(GETDATE()), GETDATE()) set @firstNextMonth = DATEADD(m, 1, DATEADD (d, 1 - DAY(GETDATE()), GETDATE())) set @daysInMonth = DATEDIFF(day,@firstMonth, @firstNextMonth) select @daysInMonth