Find End-Of-Month (EOMONTH) date from SQL

Hi Friends, have you ever struggled to find the end of the month date; I know its not a big deal you can get it from Calendar but what if you need that date for some business logic of yours? May be for Invoice processing or some other scheduling within SQL Server and you want to store \process that date? Yes, it becomes time consuming. Thankfully SQL Server has this (EOMONTH) datetime function which can be used to calculate the end of the month date automatically for any given month.
Function:EOMONTH (this functions is available in Azure SQL Databases & starting SQL Server 2012)

this takes two parameters

a. Startdate → SQL Server basically picks month from this date and calculates the end of month.

b. Month-to-add → this is an optional parameter which can help us move months back and forth from the start date, it comes handy if you want to add or subtract couple of months.
Use Case: I am working on a module which checks if my referral joined the organization or not, and if he joined we need to credit referral bonus after a quarter. Now the challenge was to find the exact end of month when this transaction will take place. So the joining date of the candidate will be used as start date and Month-to-add will be +3; just like identity you can also put a negative value if you want to calculate end of month for previous month e.g., -3

 

We can use EOMONTH function in following ways:

Select EOMONTH(Getdate(),-3)

Select EOMONTH(‘2016-04-01)

 

 

Hope you liked the post.

Happy Learning!

feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox
Follow @SQLChamp