How to use greater than operator with date?

Question

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?

Is your start_date column of type date or timestamp ? – Shamis Shukoor Nov 19 '12 at 05:50 — Shamis Shukoor, Nov 19 '12 at 05:50

score 224 · Accepted Answer · edited May 09 '19 at 10:42

224

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

SQLFiddle Demo

edited May 09 '19 at 10:42

Daniele Vrut

2,725
2
20
32

answered Nov 19 '12 at 05:49

John Woo

249,283
65
481
481

3

what happens if its timestamp? – ichimaru Jul 19 '18 at 06:16
It bears noting that MySQL seems a bit picky about the date format; while either 2019/02/08 21:04:07 or 2019-02-08 21:04:07 produces the expected outcome, 02-08-2019 21:04:07, using the US date format, casts a much wider net. – David A. Gray Feb 09 '19 at 20:08

score 26 · Answer 2 · answered Nov 19 '12 at 06:22

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.

score 12 · Answer 3 · edited Nov 19 '12 at 09:17

12

Try this.

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

edited Nov 19 '12 at 09:17

Sami

7,844
7
62
93

answered Nov 19 '12 at 08:11

Faizan Khattak

852
8
22

score 3 · Answer 4 · answered Jan 03 '20 at 04:58

3

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

answered Jan 03 '20 at 04:58

Suresh Kerai

871
1
6
20

score 3 · Answer 5 · answered Oct 13 '20 at 10:59

3

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.

answered Oct 13 '20 at 10:59

dilantha111

1,178
1
15
19

score 2 · Answer 6 · answered Jun 19 '19 at 09:22

2

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

answered Jun 19 '19 at 09:22

beatusfk

421
5
4

score 0 · Answer 7 · answered Apr 11 '21 at 00:28

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.

How to use greater than operator with date?

7 Answers7

Linked

Related