Alex Rivera | Logout

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

Asked 2009-03-27T18:46:25.627
120

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?

Edit
Report

1 Answer

0

I know this question is old but I thought I would share what I'm using.

DECLARE @date date = '2011-12-22'

/* FindFirstDayOfMonth - Find the first date of any month */
-- Replace the day part with -01
DECLARE @firstDayOfMonth date = CAST( CAST(YEAR(@date) AS varchar(4)) + '-' + 
                                      CAST(MONTH(@date) AS varchar(2)) + '-01' AS date)
SELECT @firstDayOfMonth

and

DECLARE @date date = '2011-12-22'

/* FindLastDayOfMonth - Find what is the last day of a month - Leap year is handled by DATEADD */
-- Get the first day of next month and remove a day from it using DATEADD
DECLARE @lastDayOfMonth date = CAST( DATEADD(dd, -1, DATEADD(mm, 1, FindFirstDayOfMonth(@date))) AS date)

SELECT @lastDayOfMonth

Those could be combine to create a single function to retrieve the number of days in a month if needed.

answered 2011-12-23T04:12:14.140

Your Answer