Igor Micev (10/1/2014)
You can use this function
CREATE FUNCTION dbo.fnGetMonthDays(@myDate DATE) RETURNS INT
AS
BEGIN
DECLARE @isLeap INT = 0
IF (YEAR(@myDate) % 400 = 0 OR (YEAR(@myDate) % 4 = 0 AND YEAR(@myDate) % 100 !=0))
SET @isLeap=1
DECLARE @month INT = MONTH(@myDate)
DECLARE @days INT
SELECT @days =
CASE
WHEN @month=1 THEN 31
WHEN @month=2 THEN 28 + @isLeap
WHEN @month=3 THEN 31
WHEN @month=4 THEN 30
WHEN @month=5 THEN 31
WHEN @month=6 THEN 30
WHEN @month=7 THEN 31
WHEN @month=8 THEN 31
WHEN @month=9 THEN 30
WHEN @month=10 THEN 31
WHEN @month=11 THEN 30
WHEN @month=12 THEN 31
END
RETURN @days
END
SELECT dbo.fnGetMonthDays(CONVERT(DATE,'2014-02-01'))
--28
SELECT dbo.fnGetMonthDays(CONVERT(DATE,'2016-02-01'))
--29
Why? You can make an inline table-valued function from
SELECT MonthEndDate = DATEPART(DAY,DATEADD(DAY,-1,DATEADD(m,1+DATEDIFF(m,0,MyDate),0)))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden