Reputation: 75
I'm trying to set a query that will pull all clients whose birthday is today. I'm using phpmyadmin
localhost
via Unix socket
, birthday is set as DATE
.
All I need returned is the firstname (emp.emp_firstname as
first name)
, lastname (emp.emp_lastname as
last name)
and date of birth (emp_birthday as 'dob')
Upvotes: 1
Views: 1452
Reputation: 49089
A simple solution would be like this:
SELECT
emp_firstname AS first_name,
emp_lastname AS last_name,
emp_birthday AS dob
FROM
emp
WHERE
MONTH(emp_birthday)=MONTH(CURDATE())
AND DAY(emp_birthday)=DAY(CURDATE())
or if you want to consider also leap years, you could use this:
SELECT
emp_firstname AS first_name,
emp_lastname AS last_name,
emp_birthday AS dob
FROM
emp
WHERE
emp_birthday +
INTERVAL
YEAR(CURDATE())-YEAR(emp_birthday) +
(MONTH(emp_birthday)<MONTH(CURDATE())
OR (MONTH(emp_birthday)=MONTH(CURDATE()) AND DAY(emp_birthday)<DAY(CURDATE())))
YEAR = CURDATE()
If someone's date of birth is on 29th of February, and today is 28th of February and this year is not a leap year, my last query will consider his/her birthday as today.
My second query could be also simplified like this:
SELECT
emp_firstname AS first_name,
emp_lastname AS last_name,
emp_birthday AS dob
FROM
emp
WHERE
(MONTH(emp_birthday)=MONTH(CURDATE())
AND DAY(emp_birthday)=DAY(CURDATE()))
OR (DAY(LAST_DAY(emp_birthday))=29
AND DAY(emp_birthday)=29
AND DAY(LAST_DAY(CURDATE()))=28);
Upvotes: 5
Reputation: 29809
Sorry but why not just
SELECT
emp_firstname AS first_name,
emp_lastname AS last_name,
emp_birthday AS dob
FROM rmp
WHERE DATE(emp_birthday) = CURDATE();
?
Upvotes: 0
Reputation: 1763
select emp_firstname as first_name,
emp_lastname as last_name,
emp_birthday as dob
from emp
where DATE_FORMAT(emp_birthday,'%m-%d') = DATE_FORMAT(NOW(),'%m-%d')
Upvotes: 1
Reputation: 204924
select emp_firstname as first_name,
emp_lastname as last_name,
emp_birthday as dob
from emp
where month(emp_birthday) = month(curdate())
and day(emp_birthday) = day(curdate())
Upvotes: 5
Reputation: 108839
You may be running into a problem with date matching. Curdate() returns a midnight timestamp, but your dates of birth might not themselves be midnight timestamps for reasons hard to guess. Try this:
SELECT emp_firstname AS first name,
emp_lastname AS last name,
emp_birthday AS dob
FROM emp
WHERE emp_birthday >= curdate()
AND emp_birthday < curdate() + INTERVAL 1 DAY
Upvotes: 0