{"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 &#8211; Calculating number of days in a month"},"content":{"rendered":"<p style=\"text-align: justify;\">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<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\n\r\nCREATE FUNCTION &#x5B;dbo].&#x5B;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<p style=\"text-align: justify;\">And here is the SQL code and results from the actual implementation. All we need to do is call the function, here using &#8216;GETDATE()&#8217; as an input parameter and repeating it a few times using &#8216;DATEADD&#8217; function to generate few more months in a row.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\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 &#x5B;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 &#x5B;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 &#x5B;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 &#x5B;Month\/Year],\r\ndbo.fun_GetDaysInMonth (dateadd(month,3,GETDATE())) as Number_of_Days;\r\nGO\r\n<\/pre>\n<p style=\"text-align: center;\"><a href=\"http:\/\/bicortex.com\/sql-calculating-number-of-days-in-a-month\/sql_results_fun_getdaysinmonth\/\" rel=\"attachment wp-att-170\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-170\" style=\"border-width: 0px; border-color: currentColor; border-style: none;\" title=\"SQL_Results_fun_GetDaysInMonth\" src=\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2012\/01\/SQL_Results_fun_GetDaysInMonth.png\" alt=\"\" width=\"377\" height=\"205\" srcset=\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/2012\/01\/SQL_Results_fun_GetDaysInMonth.png 377w, http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/2012\/01\/SQL_Results_fun_GetDaysInMonth-300x163.png 300w\" sizes=\"auto, (max-width: 377px) 100vw, 377px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/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":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/81","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/comments?post=81"}],"version-history":[{"count":67,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/81\/revisions"}],"predecessor-version":[{"id":192,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/81\/revisions\/192"}],"wp:attachment":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/media?parent=81"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/categories?post=81"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/tags?post=81"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}