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 238 · Accepted Answer · edited May 09 '19 at 10:42

238

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,835
2
22
32

answered Nov 19 '12 at 05:49

John Woo

258,903
69
498
492

4

what happens if its timestamp? – KD.S.T. 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

8,168
9
66
99

answered Nov 19 '12 at 08:11

Faizan Khattak

862
8
22

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

4

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

891
1
6
20

dilantha111 · Answer 5 · 2023-07-29T07:11:23.547

4

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.

edited Jul 29 '23 at 07:11

answered Oct 13 '20 at 10:59

dilantha111

1,388
1
17
19

1

you would then start out by running the date function on all rows in table la_schedule. then comparing it to the right side of the comparison - if its a large dataset - this could give you trouble – Nikolaj Hansen Jul 27 '23 at 09:36
@NikolajHansen. when thinking back again. Yes seems like it might have an optimization issue. I agree. I'll just amend the concern to the answer. Thanks – dilantha111 Jul 29 '23 at 07:10

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

451
6
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