How to use greater than operator with date?
MysqlDateOperatorsMysql Problem Overview
No idea what is going on here. Here is the query, right from phpMyAdmin:
SELECT * FROM `la_schedule` WHERE 'start_date' >'2012-11-18';
But I consistently get all records in the table returned, including those with start date 2012-11-01. What gives?
Mysql Solutions
Solution 1 - Mysql
you have enlosed start_date
with single quote causing it to become string, use backtick
instead
SELECT * FROM `la_schedule` WHERE `start_date` > '2012-11-18';
Solution 2 - Mysql
In your statement, you are comparing a string called start_date with the time.
If start_date is a column, it should either be
SELECT * FROM la_schedule WHERE start_date >'2012-11-18';
(no apostrophe)
or
SELECT * FROM la_schedule WHERE start_date >'2012-11-18';
(with backticks). Hope this helps.
Solution 3 - Mysql
Try this.
SELECT * FROM la_schedule WHERE `start_date` > '2012-11-18';
Solution 4 - Mysql
I have tried but above not working after research found below the solution.
SELECT * FROM my_table where DATE(start_date) > '2011-01-01';
Solution 5 - Mysql
Adding this since this was not mentioned.
SELECT * FROM `la_schedule` WHERE date(start_date) > date('2012-11-18');
Because that's what actually works for me. Adding date() function on both comparison values.
Solution 6 - Mysql
In my case my column was a datetime it kept giving me all records. What I did is to include time, see below example
SELECT * FROM my_table where start_date > '2011-01-01 01:01:01';
Solution 7 - Mysql
If you are comparing timestamp - you could try following
select * from table where columnInTimestamp > ((UNIX_TIMESTAMP() * 1000) - (1*24*60*60*1000))
Here UNIX_TIMESTAMP()gives current timestamp where as "12460601000" is the timestamp for 1 day -- With this you can find data just got created in 1 day or 2 days etc.