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 EOMONTH() function that takes a start date and optional months to add as input and returns the end of the month as a date.
The examples below are sample calls to the function. All inputs are valid or positive test cases.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
/* Find the start / end of each month (2014) */ -- Declare variables DEClARE @CNT1 INT = 0; DECLARE @DT1 DATETIME2 = '2014-01-10 12:32:10.12'; DECLARE @DT2 DATETIME2 = NULL; -- Demonstrate end of month WHILE (@CNT1 < 12) BEGIN -- Start of month SELECT @DT2 = DATEADD(M, @CNT1, DATEFROMPARTS(YEAR(@DT1), MONTH(@DT1), 1)); PRINT SUBSTRING(CONVERT(VARCHAR(20), @DT2, 113), 1, 11); -- End of month SELECT @DT2 = EOMONTH(DATEADD(M, @CNT1, @DT1)); PRINT SUBSTRING(CONVERT(VARCHAR(20), @DT2, 113), 1, 11); PRINT '' -- Move to next month SELECT @CNT1 = @CNT1 + 1; END GO |
The output from the test is listed below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
output: 01 Jan 2014 31 Jan 2014 01 Feb 2014 28 Feb 2014 01 Mar 2014 31 Mar 2014 01 Apr 2014 30 Apr 2014 01 May 2014 31 May 2014 01 Jun 2014 30 Jun 2014 01 Jul 2014 31 Jul 2014 01 Aug 2014 31 Aug 2014 01 Sep 2014 30 Sep 2014 01 Oct 2014 31 Oct 2014 01 Nov 2014 30 Nov 2014 01 Dec 2014 31 Dec 2014 |
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.
In a nutshell, the end of month function EOMONTH() is a brand new function that can save you some typing. However, it is only available in SQL Server 2012 or greater.