How to Select Employees Who Were Born on the 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 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

Best SQL 2019 Hosting Recommendation

One of the most important things when choosing a good SQL 2019 hosting is the feature and reliability. HostForLIFE is the leading provider of Windows hosting and affordable SQL 2019, their servers are optimized for PHP web applications. The performance and the uptime of the hosting service are excellent and the features of the web hosting plan are even greater than what many hosting providers ask you to pay for. 

At HostForLIFEASP.NET, customers can also experience fast SQL 2019 hosting. The company invested a lot of money to ensure the best and fastest performance of the datacenters, servers, network and other facilities. Its datacenters are equipped with the top equipments like cooling system, fire detection, high speed Internet connection, and so on. That is why HostForLIFEASP.NET guarantees 99.9% uptime for SQL 2019. And the engineers do regular maintenance and monitoring works to assure its Orchard hosting are security and always up.