EOMONTH

What is EOMONTH?

Date manipulations are always associated with pain for the inclined Ninja, as we have already noticed several times during the year. Therefore, there are numerous functions that can help. EOMONTH also belongs in this category and calculates the end of the respective month for a given date.

How can you work with EOMONTH?

Let’s start by calculating the end of the current month. You do that as follows:

SELECT EOMONTH(GETDATE())

With that we could end a Lightning ninja here and leave you for this year, but we would rather do a little more research on EOMONTH and give some practical examples of its use.

The first of the following month you’ll find out the following month consequently:

SELECT DATEADD(DAY, 1, EOMONTH(GETDATE()))

And the first of this month then logically so:

SELECT DATEADD(DAY, 1, EOMONTH(DATEADD(MONTH, -1, GETDATE())))

Of course you can also check that it also works in leap years:

SELECT EOMONTH('2020-02-02')

You can determine all firsts of the last 12 months as follows:

SELECT 
     DATENAME(
          WEEKDAY
         ,DATEADD(DAY, 1, EOMONTH(DATEADD(MONTH, -m, GETDATE()))))
    ,DATENAME(
         MONTH
        ,DATEADD(DAY, 1, EOMONTH(DATEADD(MONTH, -m, GETDATE()))))
FROM (
    VALUES 
         (1)
        ,(2)
        ,(3)
        ,(4)
        ,(5)
        ,(6)
        ,(7)
        ,(8)
        ,(9)
        ,(10)
        ,(11)
        ,(12)
    ) AS t(m)

You can also shorten this by using the second argument of EOMONTH, which specifies how many months you want to move away from the given date:

SELECT 
     DATENAME(
          WEEKDAY
         ,DATEADD(DAY, 1, EOMONTH(GETDATE(), -m)))
    ,DATENAME(
         MONTH
        ,DATEADD(DAY, 1, EOMONTH(GETDATE(), -m)))
FROM (
    VALUES 
         (1)
        ,(2)
        ,(3)
        ,(4)
        ,(5)
        ,(6)
        ,(7)
        ,(8)
        ,(9)
        ,(10)
        ,(11)
        ,(12)
    ) AS t(m)

That means here you have even included a rudimentary date arithmetic. All in all, EOMONTH makes your life much easier, especially when it comes to calculating deadlines. In this sense we wish you a nice

SELECT EOMONTH(GETDATE())

A happy New Year on the

SELECT DATEADD(DAY, 1, EOMONTH(GETDATE()))

And thanks for following the ninja through 2020.

References

• Documentation from Microsoft