0

My updated field is a DATETIME and that is what I want it to be.

$date = '2020-03-24';
$sql = "
SELECT * FROM test
WHERE DATE_FORMAT(updated, '%Y-%m-%d') = " . $date. "
";
$stmt = $db->query($sql);
$rows = $stmt->fetchAll();

I get an empty result when doing the above.

Question

How can I get the results where the date is from $date no matter what the time is? If I could use PHP I could use substr but this is not possible in sql.

GMB
  • 216,147
  • 25
  • 84
  • 135
Jens Törnell
  • 23,180
  • 45
  • 124
  • 206
  • why not just use `00:00:00`? satisfies MySQL and your problem without much more work. Or are you trying to get records from a date? In which case, I think there's a dupe somewhere – treyBake Mar 26 '20 at 16:28
  • 4
    Just a note that in your SQL, the date needs to be in quotes (`'" . $date ."'`) although you should be using prepared statements – Nigel Ren Mar 26 '20 at 16:31

2 Answers2

1

An efficient method would be:

where updated >= :date and updated < :date + interval 1 day

... where :date is the date parameter coming from your application.

A less efficient method is:

where date(updated) >= :date

This requires applying a date function on each and every record before filtering, which prevents the database from taking advantage of an existing index on the date column.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • The first one does not work because $date is a string, not in integer. Therefor it can't compare it if it's bigger or smaller than updated. – Jens Törnell Mar 27 '20 at 07:23
  • @JensTörnell: MySQL understands date arithmetics... The first solution shoul work and be more efficient than the second, did you actually try it? – GMB Mar 27 '20 at 08:54
1
$sql = "
SELECT * FROM test
WHERE DATE_FORMAT(updated, '%Y-%m-%d') = '" . $date. "'
";

should work

treyBake
  • 6,440
  • 6
  • 26
  • 57
ANISH KUMAR MOURYA
  • 307
  • 1
  • 7