Mysql select users which have birthday current month

Question

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

Can you change the DB? Or you can't do it, and now you are looking for solution? — Erman Belegu, Jul 26 '13 at 11:21
[This link](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html) has everything you need. Please spend sometime on these materials before you proceed — Akhil, Jul 26 '13 at 11:30

score 10 · Answer 1 · edited May 23 '17 at 11:47

10

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

edited May 23 '17 at 11:47

Community

1
1

answered Jul 26 '13 at 11:21

Jason McCreary

71,546
23
135
174

any help on converting? – Paulo José Oliveira Rosa Jul 26 '13 at 11:26
Check the link or do a Google search. But you'll want to add a new `date` column, migrate the `varchar` to a `date`, then remove the old `varchar` column. Rename the new `date` column if necessary. – Jason McCreary Jul 26 '13 at 11:26

score 9 · Accepted Answer · edited Jul 26 '13 at 11:30

9

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

edited Jul 26 '13 at 11:30

Toretto

4,721
5
27
46

answered Jul 26 '13 at 11:21

user4035

22,508
11
59
94

`NOW()` is a function, not a constant. – Jason McCreary Jul 26 '13 at 11:24
but it's still the best answer – jaczes Jul 26 '13 at 11:28
@jaczes, it indeed answers the *current* question. But *best* is debatable. – Jason McCreary Jul 26 '13 at 11:31
@PauloJoséOliveiraRosa Maybe, it didn't work because of my typo with brackets? Let me know about your table structure. Must work. – user4035 Jul 26 '13 at 11:33
the data is stored in varchar colmun in this format dd/mm/YYYY – Paulo José Oliveira Rosa Jul 26 '13 at 11:36
@PauloJoséOliveiraRosa What result did you get from mysql? I can't give you more until I see the database structure. – user4035 Jul 26 '13 at 11:37
Warning: Invalid argument supplied for foreach() in – Paulo José Oliveira Rosa Jul 26 '13 at 11:44
check my answer might help you – Tushar Gupta - curioustushar Jul 26 '13 at 11:45
@TusharGupta It will give the same result as my request – user4035 Jul 26 '13 at 11:45
U have the best answer tyvm i got this working using this SELECT * FROM jos_gerencia_clientes where date_format(str_to_date(aniversario, '%d/%m/%Y'),'%d/%m/%Y') = MONTH(NOW()) – Paulo José Oliveira Rosa Jul 26 '13 at 11:46
date_format(str_to_date(DOB, '%m/%d/%Y'), '%m') will give the month – Tushar Gupta - curioustushar Jul 26 '13 at 11:47
go it sorry my mistake – Tushar Gupta - curioustushar Jul 26 '13 at 11:57

score 2 · Answer 3 · answered Jul 26 '13 at 11:33

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

score 1 · Answer 4 · answered Jul 26 '13 at 11:23

1

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

answered Jul 26 '13 at 11:23

AlexP

9,906
1
24
43

the year part is only neccessary when you want someone born in a specific year and month, not just month – Garytje Jul 26 '13 at 11:28
@Garytje I interpreted the "current month" as the month we are currently in (meaning this year) – AlexP Jul 26 '13 at 11:31

manoj singh · Answer 5 · 2016-08-02T11:05:24.083

1

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/').'%';

edited Aug 02 '16 at 11:05

answered Aug 02 '16 at 10:49

manoj singh

338
2
9

score 0 · Answer 6 · answered Jul 26 '13 at 11:24

0

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

answered Jul 26 '13 at 11:24

Naveen Kumar Alone

7,536
5
36
57

score 0 · Answer 7 · answered Jan 02 '20 at 07:42

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

Mysql select users which have birthday current month

7 Answers7