CODEWITHSUNDEEP
mysqlphpmyadminunix-timestamp
user2381420
user2381420

Reputation: 75

Return data whose birthday is today

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 asfirst name) , lastname (emp.emp_lastname aslast name) and date of birth (emp_birthday as 'dob')

Upvotes: 1

Views: 1452

Answers (5)

fthiella
fthiella

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

RandomSeed
RandomSeed

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

Goutam Pal
Goutam Pal

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

juergen d
juergen d

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

SQLFiddle demo

Upvotes: 5

O. Jones
O. Jones

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

Related Questions