SQL – Calculating number of days in a month

Here is a little function which I found myself using a number of times (particularly to calculate averages over the length of a specific month) which calculates number of days in any given month. All you need to do, is to provider it with a datetime input parameter to return number of days as an integer.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fun_GetDaysInMonth] (@pDate DATETIME )
RETURNS INT
AS
BEGIN
    RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
                WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30
                ELSE CASE WHEN (YEAR(@pDate) % 4    = 0 AND
                                YEAR(@pDate) % 100 != 0) OR
                               (YEAR(@pDate) % 400  = 0)
                          THEN 29
                          ELSE 28
                     END
           END
END
GO

And here is the SQL code and results from the actual implementation. All we need to do is call the function, here using ‘GETDATE()’ as an input parameter and repeating it a few times using ‘DATEADD’ function to generate few more months in a row.

------------------------------------------------------------------------
--	  					DAYS IN JANUARY 2012							|
------------------------------------------------------------------------
SELECT convert (nvarchar (10), datename(month,'2012/01/01')) + ' '
+ convert (nvarchar (10), datepart(year,'2012/01/01')) as [Month/Year],
dbo.fun_GetDaysInMonth (GETDATE()) as Number_of_Days;

------------------------------------------------------------------------
--						DAYS IN FEBRUARY 2012							|
------------------------------------------------------------------------
SELECT convert (nvarchar (10), datename(month,'2012/02/01')) + ' '
+ convert (nvarchar (10), datepart(year,'2012/02/01')) as [Month/Year],
dbo.fun_GetDaysInMonth (dateadd(month,1,GETDATE())) as Number_of_Days;

------------------------------------------------------------------------
--						DAYS IN MARCH 2012								|
------------------------------------------------------------------------
SELECT convert (nvarchar (10), datename(month,'2012/03/01')) + ' '
+ convert (nvarchar (10), datepart(year,'2012/03/01')) as [Month/Year],
dbo.fun_GetDaysInMonth (dateadd(month,2,GETDATE())) as Number_of_Days;

------------------------------------------------------------------------
--						DAYS IN APRIL 2012								|
------------------------------------------------------------------------
SELECT convert (nvarchar (10), datename(month,'2012/04/01')) + ' '
+ convert (nvarchar (10), datepart(year,'2012/04/01')) as [Month/Year],
dbo.fun_GetDaysInMonth (dateadd(month,3,GETDATE())) as Number_of_Days;
GO

Submit Article :- BlinkList + Blogmarks + Digg + Del.icio.us + Ekstreme Socializer + Feedmarker + Furl + Google Bookmarks + ma.gnolia + Netvouz + RawSugar + Reddit + Scuttle + Shadows + Simpy + Spurl + Technorati + Unalog + Wink

http://scuttle.org/bookmarks.php/pass?action=add

Posted in: SQL

Tags: ,

This entry was posted on Friday, January 6th, 2012 at 1:18 pm and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply