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

SQL SERVER - Denali - Date and Time Functions - EOMONTH() - A Quick Introduction eomonth1
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;

SQL SERVER - Denali - Date and Time Functions - EOMONTH() - A Quick Introduction eomonth2
Example 3: Use of EOMONTH() function to find last day Previous and Next Month

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

SQL SERVER - Denali - Date and Time Functions - EOMONTH() - A Quick Introduction eomonth3
Example 4: Use of EOMONTH() function to find last day Previous and Next Month

SELECT DATENAME(dw,EOMONTH(GETDATE())) LastDayofMonthDay;
SQL SERVER - Denali - Date and Time Functions - EOMONTH() - A Quick Introduction eomonth4
Example 5: Get First Date of Next Week

SELECT DATEADD(d,1,EOMONTH(GETDATE())) NextMonthFirstDay;
SQL SERVER - Denali - Date and Time Functions - EOMONTH() - A Quick Introduction eomonth5
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;
SQL SERVER - Denali - Date and Time Functions - EOMONTH() - A Quick Introduction eomonth6
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)

SQL Function, SQL Scripts
Previous Post
SQL SERVER 2012 – DateTime Functions – DATEFROMPARTS() – DATETIMEFROMPARTS() – DATETIME2FROMPARTS()
Next Post
SQL SERVER 2012 Functions – 14 New Functions – A Quick Guide

Related Posts

7 Comments. Leave new

  • Fernando Bártolo
    September 20, 2011 12:13 pm

    Wow this would have come in handy a couple of months ago :)

    Is there any eta on the final version for Denali?

    Best regards, and keep up the great work!

    Reply
  • Three beers and using an android tablet, but this should be close to an end of month function , no denali required

    Create function fnEOM(@d datetime) returns datetime
    As
    Return
    Dateadd(day,-1,Dateadd(month,1,Dateadd(day,-Datepart(day,@d)+1,@d

    Should strip time as well, I will leave as exercise :-)

    Reply
  • Wow! Thanks a lot sir. Great Article.

    Reply
  • Hi Pinal,
    This is a great post for EOMonth() in SQL Server – Denali, As I’ve a ??? on example 5 with Replace(). I believe which isn’t a dynamic way to find NextMonthofFirstDay, what if you have 03/31/2011 instead of getdate(), then EOMonth() will return 04/30/2011 & then you don’t have any replacement for 30, right?

    Does this make perfect sense to you or not ??

    Reply
  • Many ways to “skin a cat”

    — function
    ALTER FUNCTION fn_EOMONTH
    (
    @MyDate datetime
    )
    RETURNS DATETIME
    AS
    BEGIN
    SET @MyDate = dateadd(m,1,@MyDate)
    RETURN ( @MyDate-datepart(d,@MyDate) )
    END

    — function call example
    SELECT dbo.fn_EOMONTH(’25/10/2011′)

    Reply

Leave a Reply