{"id":81,"date":"2012-01-06T13:18:08","date_gmt":"2012-01-06T13:18:08","guid":{"rendered":"http:\/\/bicortex.com\/?p=81"},"modified":"2012-01-12T12:03:52","modified_gmt":"2012-01-12T12:03:52","slug":"sql-calculating-number-of-days-in-a-month","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/sql-calculating-number-of-days-in-a-month\/","title":{"rendered":"SQL – Calculating number of days in a month"},"content":{"rendered":"

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.<\/p>\n

\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n\r\nCREATE FUNCTION [dbo].[fun_GetDaysInMonth] (@pDate DATETIME )\r\nRETURNS INT\r\nAS\r\nBEGIN\r\n    RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31\r\n                WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30\r\n                ELSE CASE WHEN (YEAR(@pDate) % 4    = 0 AND\r\n                                YEAR(@pDate) % 100 != 0) OR\r\n                               (YEAR(@pDate) % 400  = 0)\r\n                          THEN 29\r\n                          ELSE 28\r\n                     END\r\n           END\r\nEND\r\nGO\r\n<\/pre>\n
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.<\/p>\n
\r\n------------------------------------------------------------------------\r\n--\t  \t\t\t\t\tDAYS IN JANUARY 2012\t\t\t\t\t\t\t|\r\n------------------------------------------------------------------------\r\nSELECT convert (nvarchar (10), datename(month,'2012\/01\/01')) + ' '\r\n+ convert (nvarchar (10), datepart(year,'2012\/01\/01')) as [Month\/Year],\r\ndbo.fun_GetDaysInMonth (GETDATE()) as Number_of_Days;\r\n\r\n------------------------------------------------------------------------\r\n--\t\t\t\t\t\tDAYS IN FEBRUARY 2012\t\t\t\t\t\t\t|\r\n------------------------------------------------------------------------\r\nSELECT convert (nvarchar (10), datename(month,'2012\/02\/01')) + ' '\r\n+ convert (nvarchar (10), datepart(year,'2012\/02\/01')) as [Month\/Year],\r\ndbo.fun_GetDaysInMonth (dateadd(month,1,GETDATE())) as Number_of_Days;\r\n\r\n------------------------------------------------------------------------\r\n--\t\t\t\t\t\tDAYS IN MARCH 2012\t\t\t\t\t\t\t\t|\r\n------------------------------------------------------------------------\r\nSELECT convert (nvarchar (10), datename(month,'2012\/03\/01')) + ' '\r\n+ convert (nvarchar (10), datepart(year,'2012\/03\/01')) as [Month\/Year],\r\ndbo.fun_GetDaysInMonth (dateadd(month,2,GETDATE())) as Number_of_Days;\r\n\r\n------------------------------------------------------------------------\r\n--\t\t\t\t\t\tDAYS IN APRIL 2012\t\t\t\t\t\t\t\t|\r\n------------------------------------------------------------------------\r\nSELECT convert (nvarchar (10), datename(month,'2012\/04\/01')) + ' '\r\n+ convert (nvarchar (10), datepart(year,'2012\/04\/01')) as [Month\/Year],\r\ndbo.fun_GetDaysInMonth (dateadd(month,3,GETDATE())) as Number_of_Days;\r\nGO\r\n<\/pre>\n
\"\"<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[12,49],"class_list":["post-81","post","type-post","status-publish","format-standard","hentry","category-sql","tag-code","tag-sql"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t