SQL SERVER – Finding If Date is LastDay of the Month

As I said many times before SQL Server does not stop amazing me continuously. It does not matter how many questions and issues I have come across so far there are always new things to learn.

Here is the question which I was asked – How do we know if any of the employees joined on the last day of the month. This was interesting for sure. It is easy to know if an employee has joined the first day of the month or not as it is always date 1. However, for any month there is no way to know the last date as it can be any of the following: 28, 29, 30, 31. The safe bet is that we add 1 to the date and check if that is 1 or not. It is quite popular and I have used that in the past.

However, from SQL Server 2012 we have a new function called EOMONTH and we can use the same to check the values as well. Here is how we can check the hire date of the employee if it is the last day or not.

Solarwinds

SQL Server 2012 and later version: 

SELECT
[LoginID] ,[HireDate],
IIF([HireDate] = EOMONTH([HireDate]), 'LastDay', '') LastDay
FROM [AdventureWorks2014].[HumanResources].[Employee]

Earlier version of SQL Server 2012:

SELECT
[LoginID] ,[HireDate] ,CASE  DAY(DATEADD(d,1,[HireDate]))
WHEN 1 THEN 'LastDay'
ELSE ''
END 'LastDay'
FROM [AdventureWorks2014].[HumanResources].[Employee]

Both of the above queries will give following resultset:

SQL SERVER - Finding If Date is LastDay of the Month lastday

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

Solarwinds
Previous Post
SQL SERVER – Scope of ERROR_MESSAGE
Next Post
SQL SERVER – Failed Rule “Valid DSN” and “Valid Database compatibility level and successful connection”

Related Posts

4 Comments. Leave new

  • Pinal, they are very good methods. Here are two more methods that do the same thing

    Reply
  • Sébastien Sevrin
    September 24, 2015 1:52 pm

    I agree that EOMONTH is useful because it saves some typing, but it has to be improved to change tricks like DATEADD(D, -1, @FirstDayOfMonthPlus1) to EOMONTH(@Date) in all queries.
    I’m thinking about:
    – Making it deterministic
    – Completing it with BOMONTH, (B|E)OYEAR, (B|E)ODAY

    Reply
  • Datediff(month, thedate, dateadd(day, 1, thedate)) = 1

    Reply
  • Here’s one

    How can I return the number of months an item has been on contract with relation to the Invoice Start and End range within the same query?

    User range selection:
    Invoice Start Date: 2/1/2015
    Invoice End Date: 9/28/2015

    Item1
    Contract Start Date: 3/1/2015
    Contract End Date: 4/28/2015
    Item2
    Contract start Date: 1/1/2015
    Contract End Date: NULL
    Item 3
    Contract Start Date: 8/1/2015
    Contract End Date: NULL
    Item 4
    Contract Start Date: 1/1/2015
    Contract End Date: 3/31/2015

    Reply

Leave a Reply

Menu