CODEWITHSUNDEEP
mysql
Paulo José Oliveira Rosa
Paulo José Oliveira Rosa

Reputation: 509

Mysql select users which have birthday current month

I have stored in the database in a varchar column the birthdays like this

dd/mm/YYYY

How can I select the birthday people from the current month directly from MySQL query??

And show using PHP

Thanks

Upvotes: 0

Views: 15245

Answers (7)

Atul Baldaniya
Atul Baldaniya

Reputation: 879

I have store dates as a timestamp in tables so I create a query like this. and it's working fine.

don't use varchar for a date. 

select first_name, last_name, date_format(FROM_UNIXTIME(`dateofbirth`), '%m/%d/%Y') as dob from users where date_format(FROM_UNIXTIME(`dateofbirth`), '%m') = MONTH(NOW())

Upvotes: 0

manoj singh
manoj singh

Reputation: 346

For Get User list whose birthday in current month in mysql if field datatype is date 
        $Toaday = '%'.date('-m-').'%';

        $query = " select * from client where birth_date LIKE '$Toaday' ";

    In your case declare $Today = '%'.date('/m/').'%';

Upvotes: 1

Tushar Gupta - curioustushar
Tushar Gupta - curioustushar

Reputation: 57095

Assuming date is stored in %m/%d/%Y this format you can change this format according to your need.

and %m we are selecting only the month and comparing it to the current month MONTH(NOW()).

Replace DOB by your column and table by your table name

select * from table
  where date_format(str_to_date(DOB, '%m/%d/%Y'), '%m') = MONTH(NOW());;

Upvotes: 2

user4035
user4035

Reputation: 23749

SELECT
    *
FROM
    yourtable
WHERE
    MONTH(STR_TO_DATE(yourdatefield, '%d/%m/%Y')) = MONTH(NOW())

Upvotes: 9

Naveen Kumar Alone
Naveen Kumar Alone

Reputation: 7668

As you used a VARCHAR field instead a DATE field,

you have to cast the value and then use the normal date functions. like

SELECT * 
   FROM table_name 
   WHERE MONTH(CAST(col_name AS DATE)) = MONTH(NOW());

Upvotes: 0

AlexP
AlexP

Reputation: 9857

You should change your column type to DATE. e.g.

ALTER TABLE `people` CHANGE  `dob` `dob` DATE NOT NULL;

By doing so you can then use the MySQL query date functions to filter the results.

SELECT * FROM people WHERE MONTH(dob) = MONTH(CURDATE()) AND YEAR(dob) = YEAR(CURDATE())

Upvotes: 1

Jason McCreary
Jason McCreary

Reputation: 73001

First, do not store dates as a VARCHAR. Convert it to a DATE.

Once that's fixed, use one of the many MySQL date time functions:

SELECT * FROM users WHERE MONTH(birthday) = MONTH(NOW());

Upvotes: 10

Related Questions