CODEWITHSUNDEEP
sqlsql-serverdatabase
Aarthna Maheshwari
Aarthna Maheshwari

Reputation: 169

Birthday reminder in SQL Server

I want to select those people whose date of birth matches with current system date.

My SQL query is:

select testtable.task 
from testtable 
where testtable.dates like (select SUBSTRING(CONVERT(varchar(10), GETDATE(), 10), 1, 5))

I have written this in SQL Server.

The issue is it is not returning any data but the inner subquery is working perfectly fine.

Please specify the solutions for both the cases when date is stored as a string and when date is stored as date type.

Thanks in advance

Upvotes: 1

Views: 1486

Answers (2)

ili
ili

Reputation: 411

Accepted solution doesn't take into account Leap years, so if someone birthday is on the 2020-02-29 he will only get a notification every 4 years.

This query returns on non leap years the 02/29 birthdays on the 02/28 like most countries and social medias.

MariaDB/MySQL:

SELECT
    *
FROM 
    Employees
WHERE
CASE
    WHEN MONTH(CURDATE()) = 2 AND DAY(CURDATE()) = 28
        AND DAY(ADDDATE(CURDATE(), 1)) != 29
    THEN MONTH(Employees.DateOfBirth) = 2 AND 
        DAY(Employees.DateOfBirth) IN (28, 29)
    ELSE MONTH(Employees.DateOfBirth) = MONTH(CURDATE()) AND 
        DAY(Employees.DateOfBirth) = DAY(CURDATE())
END

MsSQL:

SELECT
    *
FROM 
    Employees
WHERE
    (MONTH(GETDATE()) = 2 AND DAY(GETDATE()) = 28 AND DAY(DATEADD(day, 1, GETDATE())) != 29) AND
    (MONTH(Employees.DateOfBirth) = 2 AND DAY(Employees.DateOfBirth) IN (28, 29))
    OR
    NOT (MONTH(GETDATE()) = 2 AND DAY(GETDATE()) = 28 AND DAY(DATEADD(day, 1, GETDATE())) != 29) AND
    (MONTH(Employees.DateOfBirth) = MONTH(GETDATE()) AND DAY(Employees.DateOfBirth) = DAY(GETDATE()))

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82524

Assuming testtable.dates is of type Date DateTime or DateTime2, you can simply use this query:

select testtable.task 
from testtable 
where month(testtable.dates) = month(getdate())
and day(testtable.dates) = day(getdate())

Upvotes: 1

Related Questions