How to Select Employees Who Were Born on the Last Day of the Month

Introduction

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 the last day of the month.

Step 1: 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

Explanation

• 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 the function body that will hold the last day of the @DOB's month. A BIT variable @ReturnValue is declared to return a True/False (1/0) value. A SELECT statement sets the value of the @LastDayOfMonth variable that needs some attention:
   
• This SELECT statement has three parts; the first part is "DATEDIFF(m, 0, @DoB)". It returns the 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 the 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
   
• The second part is DATEADD(m, DATEDIFF(m, 0, @DoB)+1,0). It adds the number of months returned from the first step to '0' i.e., starting date '1/1/1900', that will return the first day of the next month. For example it returns a date like '1985-11-01 00:00:00.000'.
  
  Example
  
  SELECT DATEADD(mm, DATEDIFF(m, 0, '1985-10-29')+1,0)
  Result : 1985-11-01 00:00:00.000
   
• The third part is "DDATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, @DoB)+1,0))". It uses the DATEADD function to subtract one second from the date returned from last part. It will return the last date of the @DoB because the date returned in the last part is exactly midnight and subtracting a second from it will return its previous date which is the last day of the @DoB's month. You also can subtract a minute or hour.
  
  Example
   
  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 the last date of the @DoB's month in @LastDayOfMonth. In the next step we use the DATEPART function to compare the days of @LastDayOfMonth with @DoB. If it matches we set the value of @ReturnValue to 1 or true to indicate this employee is born on the last day of the month otherwise we return 0.
   
• Finally we can run the following query to return all the Employee's records born on the last day of the month:
  
  SELECT * FROM Employees WHERE dbo.CheckLastDay(DoB)=1