Post
Ask Question

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 the date of birth of the employee as a parameter and it will check if the given date is the last day of the month.

How to Create a User-Defined Function?

User-defined function to check if the given day is the last day of the month.

CREATEFUNCTIONCheckLastDay(@DoBDATETIME)
RETURNSBIT
AS
BEGIN
DECLARE@LastDayOfMonthDATETIME
DECLARE@ReturnValueBIT
SELECT@LastDayOfMonth =DATEADD(s,-1,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 date-time 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: "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', which 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 DATEDIFF(m, 0, '1985-10-29')+1,0)

    Result 1985-11-01 00:00:00.000

  • The third part is "DDATEADD,-1, DATEDIFF(m, 0, @DoB)+1,0))". It uses the DATEADD function to subtract one second from the date returned from the last part. It will replace the last date of the @DoB because the date returned in the last part is precisely 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

    SELECTDATEADD(s,-1,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: 
    SELECTFROMEmployeesWHEREdbo.CheckLastDay(DoB)=1


Similar Articles