Date comparison in where clause does not work as expected

Asked Apr 27 '20 at 12:03

Active Apr 27 '20 at 12:09

Viewed 23 times

I have a table as follows:

Now I want to count rows where ship_date is past date. And I am trying as follows:

 $today = date('yy-m-d');
 $sql = "SELECT count(*) as total FROM spot_shipment_order where date('ship_date') < '$today'";

Today's date is: 2020-04-27 and final SQL is: SELECT count(*) as total FROM spot_shipment_order where date('ship_date') < '2020-04-27'. As 2020-04-29 is not past date with respect to today's date, hence the above SQL should count 0. it returns 1. Strange!. this problem make me cry. I think I am doing some mistakes and can't explore that mistake. Any help is appreciated. Thanks in advance.

edited Apr 27 '20 at 12:04

jarlh

42,561
8
45
63

asked Apr 27 '20 at 12:03

Abdus Sattar Bhuiyan

3,016
4
38
72

1

Don't put single quotes around `ship_date`. And just use `CURRENT_DATE` in the database (or `CURRENT_TIMESTAMP` depending on what you really mean). – Gordon Linoff Apr 27 '20 at 12:04
1

Interesting that `'ship_date'` did not throw errors. Use backticks not quotes – Rotimi Apr 27 '20 at 12:06
1

Also what is the data-type for ship_date? DATE i hope – Rotimi Apr 27 '20 at 12:06
3

`date('yy-m-d');` this will only work for this year. Next year it will show 2121. You'd want `Y-m-d` for the properly formatted 2020-04-27. – aynber Apr 27 '20 at 12:07
1

How are you checking for the count after you run this query? If you're using num_rows, then yes, it will always return 1 because it's returning a count. – aynber Apr 27 '20 at 12:08
Yes, ship_date is date type – Abdus Sattar Bhuiyan Apr 27 '20 at 12:16
no need to add date(_) in quuery just where ship_date < '$today'"; – Justin J Apr 27 '20 at 12:17
Thanks everyone. I changed single quotes into backticks and it works. – Abdus Sattar Bhuiyan Apr 27 '20 at 12:22

Date comparison in where clause does not work as expected

0 Answers0