Continuing my series on SQL Server and Oracle, I thought I’d highlight a function that has been in Oracle from some time and has only just recently appeared in SQL Server.

End of Month

Getting the end of the month is a really important task for any report writer or developer that needs to write code to close out or query data by month.

Beginning with SQL Server 2012 you can easily obtain the last day of the month with the EOMONTH() function. Prior to this you needed to do some magic with DATEADD() and while this worked and was efficient it always seemed like a lacking.

The last day of the month can be retrieved in Oracle with, you guessed it: LAST_DAY(). This function has been around since Oracle 8i which came out in 1998.

Usage

SQL Server:

SELECT EOMONTH(GETDATE())

RESULT:

2017-08-31

Oracle:

SELECT last_day(sysdate) FROM dual;

Result:

31-AUG-17

 

I hope you enjoyed this simple post today. If you’d like to read more of my posts they can be found on my blog at SQLTechBlog.com. You can also find more from me on LinkedIn, Twitter, and Instagram.