{"id":6764,"date":"2013-12-08T00:00:16","date_gmt":"2013-12-08T00:00:16","guid":{"rendered":"http:\/\/craftydba.com\/?p=6764"},"modified":"2016-04-20T01:56:48","modified_gmt":"2016-04-20T01:56:48","slug":"datetime-functions-eomonth","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=6764","title":{"rendered":"Date\/Time Functions \u2013 EOMONTH()"},"content":{"rendered":"<p>I linger on to the end with my writing of short articles or tidbits on Transaction SQL <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ms186724.aspx\">date\/time functions<\/a>.<\/p>\n<p>Most, if not all, modern day computer systems collect data which contains dates and times.<\/p>\n<p>Sometimes we want to know the start date and end date of each month.  Before SQL Server 2012, you used to have to set the date to the first of next month.  Then use the DATEADD() function to back date by one day to get the end of month date.<\/p>\n<p>Today, I will be exploring the <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/hh213020.aspx\">EOMONTH()<\/a> function that takes a start date and optional months to add as input and returns the end of the month as a date. <\/p>\n<p>The examples below are sample calls to the function. All inputs are valid or positive test cases.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"eomonth() function\">\r\n\/*\r\n    Find the start \/ end of each month (2014)\r\n*\/\r\n\r\n-- Declare variables\r\nDEClARE @CNT1 INT = 0;\r\nDECLARE @DT1 DATETIME2 = '2014-01-10 12:32:10.12';\r\nDECLARE @DT2 DATETIME2 = NULL;\r\n\r\n-- Demonstrate end of month\r\nWHILE (@CNT1 &lt; 12)\r\nBEGIN\r\n\r\n  -- Start of month\r\n  SELECT @DT2 = DATEADD(M, @CNT1, DATEFROMPARTS(YEAR(@DT1), MONTH(@DT1), 1)); \r\n  PRINT SUBSTRING(CONVERT(VARCHAR(20), @DT2, 113), 1, 11);\r\n\r\n  -- End of month\r\n  SELECT @DT2 = EOMONTH(DATEADD(M, @CNT1, @DT1));\r\n  PRINT SUBSTRING(CONVERT(VARCHAR(20), @DT2, 113), 1, 11);\r\n  PRINT &#039;&#039;\r\n\r\n  -- Move to next month\r\n  SELECT @CNT1 = @CNT1 + 1;\r\nEND\r\nGO\r\n<\/pre>\n<\/p>\n<p>The output from the test is listed below. <\/p>\n<pre class=\"lang:TSQL theme:epicgeeks\" title=\"output\">\r\noutput: \r\n01 Jan 2014    31 Jan 2014\r\n01 Feb 2014    28 Feb 2014\r\n01 Mar 2014    31 Mar 2014\r\n01 Apr 2014    30 Apr 2014\r\n01 May 2014    31 May 2014\r\n01 Jun 2014    30 Jun 2014\r\n01 Jul 2014    31 Jul 2014\r\n01 Aug 2014    31 Aug 2014\r\n01 Sep 2014    30 Sep 2014\r\n01 Oct 2014    31 Oct 2014\r\n01 Nov 2014    30 Nov 2014\r\n01 Dec 2014    31 Dec 2014 \r\n<\/pre>\n<\/p>\n<p>The following rules apply when dealing with negative test cases or invalid data.  Any invalid date parameter results in a raised error.  Any NULL values passed as parameters result in a NULL result. Any EMPTY strings result in a default date.<\/p>\n<p>In a nutshell, the end of month function <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/hh213020.aspx\">EOMONTH()<\/a> is a brand new function that can save you some typing.  However, it is only available in SQL Server 2012 or greater.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I linger on to the end with my writing of short articles or tidbits on Transaction SQL date\/time functions. Most, if not all, modern day computer systems collect data which contains dates and times. Sometimes we want to know the start date and end date of each month. Before SQL Server 2012, you used to have to set the date to the first of next month. Then use the DATEADD() function to back date by one day to get the end of month date. Today, I will be exploring the&hellip;<\/p>\n","protected":false},"author":1,"featured_media":6759,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[814],"tags":[31,940,995,996,15,29],"class_list":["post-6764","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-very-short-articles","tag-database-developer","tag-date-time-functions","tag-end-of-month","tag-eomonth","tag-john-f-miner-iii","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6764","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6764"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6764\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/6759"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6764"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6764"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6764"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}