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:
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:
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
Pinal, they are very good methods. Here are two more methods that do the same thing
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
Datediff(month, thedate, dateadd(day, 1, thedate)) = 1
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