CODEWITHSUNDEEP
mysqldateoperators
Clinton J
Clinton J

Reputation: 2123

How to use greater than operator with date?

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?

Upvotes: 153

Views: 407813

Answers (7)

dilantha111
dilantha111

Reputation: 1498

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.

PS: As @NikolajHansen has pointed out in the comment, this might not be the ideal solution, since this could run the date function in every row.

Upvotes: 4

Pravin Bansal
Pravin Bansal

Reputation: 4681

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.

Upvotes: 0

Suresh Kerai
Suresh Kerai

Reputation: 921

I have tried but above not working after research found below the solution.

SELECT * FROM my_table where DATE(start_date) > '2011-01-01';

Ref

Upvotes: 6

beatusfk
beatusfk

Reputation: 451

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';

Upvotes: 2

John Woo
John Woo

Reputation: 263803

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';

Upvotes: 245

Faizan Khattak
Faizan Khattak

Reputation: 882

Try this.

SELECT * FROM la_schedule WHERE `start_date` > '2012-11-18';

Upvotes: 12

Kneel-Before-ZOD
Kneel-Before-ZOD

Reputation: 4221

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.

Upvotes: 29

Related Questions