SQL SERVER – Denali – Date and Time Functions – EOMONTH() – A Quick Introduction

In SQL Server Denali, seven new datetime functions have been introduced, namely,

• DATEFROMPARTS (year, month, day)
• DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)
• DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)
• DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
• SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
• TIMEFROMPARTS (hour, minute, seconds, fractions, precision)
• EOMONTH (start_date)

EOMONTH() is a very interesting function. It is a very common requirement in many major applications where the user needs the last day of the month. It is very easy to figure out what is the first day of the month because obviously, it is comes first anyway. However, the last day of the month keeps changing as every month has different number of days and leap year also makes this more interesting.

Let us understand this function using few examples.

Example 1: Use of EOMONTH() function to find last day of the month

SELECT EOMONTH(GETDATE()) LastDayofMonth


Example 2: Use of EOMONTH() function to find last day of the month during Leapyear

SELECT EOMONTH('20110201') NonLeapYearFebLastDay;
SELECT EOMONTH('20120201') LeapYearFebLastDay;
SELECT EOMONTH('20130201') NonLeapYearFebLastDay;

Example 3: Use of EOMONTH() function to find last day Previous and Next Month

SELECT EOMONTH(GETDATE(),-1) PreviousMonthLastDay;
SELECT EOMONTH(GETDATE(),1) NextMonthLastDay;


Example 4: Use of EOMONTH() function to find last day Previous and Next Month

SELECT DATENAME(dw,EOMONTH(GETDATE())) LastDayofMonthDay;

Example 5: Get First Date of Next Week

SELECT DATEADD(d,1,EOMONTH(GETDATE())) NextMonthFirstDay;

Have you tried the following method? I personally use the method mentioned above but once in an interview, one of the candidates gave the following answer, and I found it very interesting.

SELECT REPLACE(EOMONTH(GETDATE(),1),31,1) NextMonthFirstDay;

Well, you can also apply this function to any table column to get the desired value. I have previously written an UDF when I had the following requirement: when any user registers in an application, he should get automatically validated till the end of the month. I will now replace that custom logic/UDF with this function.

Quick Video on the same subject

[youtube=http://www.youtube.com/watch?v=BL5GO-jH3HA]

Reference:Pinal Dave (https://blog.sqlauthority.com)