Find The End Of Month In SQL Server

SQL Server 2012 has introduced a nice function EOMONTH which gives end of month for a given date.  How do you know how many days are in a month in real life? Well, for me, l still use the childhood song “30 days has September, April, June and November. All the rest has 31 except February which has 28  or 29 for a leap year”.  But knowing that SQL Server can’t sing when you pass it a date, I have to come up with code that will give end of the month for a particular date when required. In SQL 2012, I can easily pass the date to the function like  EOMONTH(‘6/28/2015’) or EOMONTH(GETDATE()). This makes life easy but what if you are using version of SQL Server that is earlier version than 2012, for example SQL Server 2008 or 2008 r2. These versions do not have suppport for EOMONTH function. So what l’m going to do is turn that childhood song into a code that l can use to find the end of the month for a give date.

Lets start by just getting the number of days in a given month from the given date. For example, lets use GETDATE() for today’s date and try to find the number of days in the month of today’s date. To find the month that we are in SQL, we can use the built in function MONTH() like MONTH(GETDATE()) gives me 6.  MONTH() returns an int which is the number of the month. As part of the song above, February has either 28 days or 29 days in a leap year. In a leap year, there are 366 days and to find out if a year is a leap year or not, you can follow the steps below copied from https://support.microsoft.com/en-us/kb/214019

1. If the year is evenly divisible by 4, go to step 2. Otherwise, go to step 5.
2. If the year is evenly divisible by 100, go to step 3. Otherwise, go to step 4.
3. If the year is evenly divisible by 400, go to step 4. Otherwise, go to step 5.
4. The year is a leap year (it has 366 days).
5. The year is not a leap year (it has 365 days).

So our code will also have the above steps formulated so we are giving the right number of days for February in the year.

Here is the code to implementation of the above song to find number of days in a month, using today’s date.

SELECT MONTH(GETDATE()) AS MonthNumber,CASE
WHEN YEAR(GETDATE())%4 = 0 AND YEAR(GETDATE())%100 = 0 AND YEAR(GETDATE()) %400 = 0
THEN CASE MONTH(GETDATE())
WHEN 4 THEN 30
WHEN 6 THEN 30
WHEN 9 THEN 30
WHEN 11 THEN 30
WHEN 2 THEN 29
ELSE 31
END
ELSE CASE MONTH(GETDATE())
WHEN 4 THEN 30
WHEN 6 THEN 30
WHEN 9 THEN 30
WHEN 11 THEN 30
WHEN 2 THEN 28
ELSE 31
END
END AS NumberOfDaysInMonth

I had MonthNumber = 6 and NumberOfDaysInMonth = 30.

Ok, Now let’s go ahead and modify the above code so that it will give the end of the month date. In SQL 2012, there is the new function DATEFROMPARTS() which you can easily pass the year, month and day and it will return a date for you. So in SQL 2012  select datefromparts(2012,02,02) will give you  the date 2012-02-02 but this doesn’t work in earlier versions of SQL Server either. What l’m getting at is l know the number of days in the month, month and we can use the YEAR() function to get the year from the given date so l can easily form the date which will be the end of the month from those information. We are going to modify the code to give us this information.

SELECT YEAR(GETDATE()) AS YEAR,MONTH(GETDATE()) AS MONTHNumber,CASE
WHEN YEAR(GETDATE())%4 = 0 AND YEAR(GETDATE())%100 = 0 AND YEAR(GETDATE()) %400 = 0
THEN CASE MONTH(GETDATE())
WHEN 4 THEN 30
WHEN 6 THEN 30
WHEN 9 THEN 30
WHEN 11 THEN 30
WHEN 2 THEN 29
ELSE 31
END
ELSE CASE MONTH(GETDATE())
WHEN 4 THEN 30
WHEN 6 THEN 30
WHEN 9 THEN 30
WHEN 11 THEN 30
WHEN 2 THEN 28
ELSE 31
END
END AS NumberOfDaysInMonth,
CAST(YEAR(GETDATE()) AS VARCHAR(4)) + ‘-‘ + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + ‘-‘ +
CAST((CASE
WHEN YEAR(GETDATE())%4 = 0 AND YEAR(GETDATE())%100 = 0 AND YEAR(GETDATE()) %400 = 0
THEN CASE MONTH(GETDATE())
WHEN 4 THEN 30
WHEN 6 THEN 30
WHEN 9 THEN 30
WHEN 11 THEN 30
WHEN 2 THEN 29
ELSE 31
END
ELSE CASE MONTH(GETDATE())
WHEN 4 THEN 30
WHEN 6 THEN 30
WHEN 9 THEN 30
WHEN 11 THEN 30
WHEN 2 THEN 28
ELSE 31
END
END) AS VARCHAR(2))AS EndOfMonthDate

 

The result gives the date of the end of month of the GETDATE().  With this information, you can build your a function that can take a date and return the end of the month date for it or you can directly use it and pass a date in place of the GETDATE() to get the end of month for that date.

There might be smarter ways to get this done, please share if you know one.