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

Reference:Pinal Dave (http://blog.SQLAuthority.com)

About these ads

13 thoughts on “SQL SERVER – Denali – Date and Time Functions – EOMONTH() – A Quick Introduction

  1. Pingback: SQL SERVER – Denali – 14 New Functions – A Quick Guide « Journey to SQLAuthority

  2. 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 :-)

  3. 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 ??

  4. 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′)

  5. Pingback: SQL SERVER – Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function « SQL Server Journey with SQL Authority

  6. Pingback: SQL SERVER – Function: Is Function – SQL in Sixty Seconds #004 – Video « SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – Get Date and Time From Current DateTime – SQL in Sixty Seconds #025 – Video « SQL Server Journey with SQL Authority

  8. Pingback: SQL SERVER – Weekly Series – Memory Lane – #046 | Journey to SQL Authority with Pinal Dave

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #047 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s