How to Select Employees who have born on last day of the month
In this article we will learn to how select employees from a SQL Server table whose day of birth is last day of the month.
In this article we will create a User Defined Function that will accept date of birth of the employee as a parameter and it will check if the given date is last day of the month.
Create a User Defined Function
-- User defined function to check if given day is last day of the month CREATE FUNCTION CheckLastDay(@DoB DATETIME) RETURNS BIT AS BEGIN DECLARE @LastDayOfMonth DATETIME DECLARE @ReturnValue BIT SELECT @LastDayOfMonth = DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, @DoB)+1,0)) IF(DATEPART(dd,@LastDayOfMonth)=DATEPART(dd,@Dob)) BEGIN SET @ReturnValue= 1 END ELSE BEGIN SET @ReturnValue= 0 END RETURN @ReturnValue END
- A function CheckLastDay is created that takes a DATETIME parameter @DOB. This function returns a BIT(0 or 1). A DATETIME variable @LastDayOfMonth is declared inside function body that will hold last day of @DOB’s month. A BIT variable @ReturnValue is declared to return True/False(1/0) value. A SELECT statement sets value of the @LastDayOfMonth variable that need some attention:
- This SELECT statement has three parts, first part is “DATEDIFF(m, 0, @DoB)”. It returns total number of months from 0 ( 0 or ‘’ indicates starting date ‘1/1/1900’) to @DoB. One is added to the returned months to get next month of @DoB.
For example if I replace @DoB with my date of birth:
SELECT DATEDIFF(m, 0, ‘1985-10-29’)+1
Result : 1030
- Second part is DATEADD(m, DATEDIFF(m, 0, @DoB)+1,0). It adds number of months returned from the first step to ‘0’ i.e., starting date ‘1/1/1900’, that will return first day of the next month. For example it returns a date like ‘1985-11-01 00:00:00.000’
SELECT DATEADD(mm, DATEDIFF(m, 0, ‘1985-10-29’)+1,0)
Result : 1985-11-01 00:00:00.000
- Third part is “DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, @DoB)+1,0))”. It uses DATEADD function to subtract one second from the date returned from last part. It will return last date of the @DoB because date returned in the last part is exactly midnight and subtracting a second from it will return its previous date which is last day of the @DoB’s month. You can also subtract a minute or hour.
SELECT DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, ‘1985-10-29’)+1,0))
Result : 1985-10-31 23:59:59.000
- Now we have last date of the @DoB’s month in @LastDayOfMonth. In next step we use DATEPART function to compare days of @LastDayOfMonth with @DoB. If it matches we set value of @ReturnValue to 1 or true to indicate this employee is born on last day of the month otherwise we return 0.
- Finally we can run following query to return all the Employees record who have born on the last day of the month
SELECT * FROM Employees WHERE dbo.CheckLastDay(DoB)=1