0

I have received a task from a client who wants some changes in his ongoing application. There is a table called users which has a field called birthdate. The previous developer was an idiot who saved the birthday varchar(255) instead of datetime. The data in the birthday column is saved as January 01, 1986.

I want to fetch all the users whose birthday is in current date.

I tried to use:

$query = "SELECT  * FROM users WHERE 
DAY(STR_TO_DATE(birthday, '%Y-%m-%d')) = '".date('d')."' 
AND MONTH(STR_TO_DATE(birthday, '%Y-%m-%d')) = '".date('m')."'";

But it is returning empty set of data. How can I fix this?

Saswat
  • 12,320
  • 16
  • 77
  • 156

3 Answers3

2

Your date format does not match the one you specified in your question. I think you're looking for:

$query = "SELECT * FROM users WHERE 
DAY(STR_TO_DATE(birthday, '%M %d, %Y')) = '".date('d')."' 
AND MONTH(STR_TO_DATE(birthday, '%M %d, %Y')) = '".date('m')."'";
Nick
  • 339
  • 1
  • 11
0

I worked it out and realized what I was doing wrong:-

$query = "SELECT * FROM users 
           WHERE DAY(date_format(str_to_date( birthday, '%M %d, %Y' ), '%Y-%m-%d' )) = '".date('d')."'
          AND MONTH(date_format(str_to_date( birthday, '%M %d, %Y' ), '%Y-%m-%d' )) = '".date('m')."'";

First we need to convert the birthday from string to compatible date, and then we need to format it to actual date.

Saswat
  • 12,320
  • 16
  • 77
  • 156
0

I would render today's date as a string in the same format the database uses and then search for that exact string. At least that way you'll get the benefit of an index if the field is indexed. (Or should you add one.) With the query as is, you'll always have to do a sequential scan.

$today = date('F d, Y');
$sql = 'SELECT * FROM users WHERE birthday = :today';

Also, use prepared statements with bound parameters, via either mysqli or PDO. This post has some good examples.

Alex Howansky
  • 50,515
  • 8
  • 78
  • 98