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.
SQL Server 2012 and later version:
IIF([HireDate] = EOMONTH([HireDate]), 'LastDay', '') LastDay
Earlier version of SQL Server 2012:
[LoginID] ,[HireDate] ,CASE DAY(DATEADD(d,1,[HireDate]))
WHEN 1 THEN 'LastDay'
Both of the above queries will give following resultset:
Reference: Pinal Dave (https://blog.sqlauthority.com)