How to Select Employees who have born on last day of the month

By | March 8, 2012

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.

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

Step 1:

Create a User Defined Function

[sourcecode language=”sql”]

— 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
[/sourcecode]

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 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’

Example:

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.

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 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

[sourcecode language=”sql”]
SELECT * FROM Employees WHERE dbo.CheckLastDay(DoB)=1
[/sourcecode]

Leave a Reply

Your email address will not be published. Required fields are marked *