What's wrong with this SQL syntax to select data between two dates?

Question

This query was working before, but maybe I changed it somehow..

select * from completed_games where date BETWEEN 2015-02-22 00:00:00 AND 2015-02-28 00:00:00

Why would this query not work, and how can I properly write it?

Error:

ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '00:00:00 AND 2015-02-28 00:00:00' at line 1

Please note that it's better to explicitly use comparison operators for a date range (rather than 'between',) since it's easier to know whether it includes the date or not by looking for an equal sign. — mbomb007, Feb 23 '15 at 15:09
@Strawberry See here, `between` is not recommended. http://stackoverflow.com/a/749663/2415524 — mbomb007, Feb 23 '15 at 21:25
@mbomb007 I think that's a spurious argument. As one of the commentator's suggests, it's like pointing out that 10.2 is not between 5 and 10. And the example provided is nonsensical. I don't understand why its attracted so much rep. — Strawberry, Feb 23 '15 at 21:43
It's definitely a readability issue. Why not be clear and use standard comparison operators. Also, funny that you couldn't even come up with your own counter-argument. — mbomb007, Feb 23 '15 at 21:49

score 2 · Answer 1 · answered Feb 23 '15 at 14:59

2

Your dates are not valid. You need to enclose them between quotes.

select *
from completed_games
where date BETWEEN '2015-02-22 00:00:00' AND '2015-02-28 00:00:00'

answered Feb 23 '15 at 14:59

A.D.

1,160
1
8
23

score 1 · Answer 2 · answered Feb 23 '15 at 14:59

1

You need to put quotes around your date strings:

select * 
from completed_games 
where date BETWEEN '2015-02-22 00:00:00' 
    AND '2015-02-28 00:00:00'

answered Feb 23 '15 at 14:59

John Conde

217,595
99
455
496

score 1 · Answer 3 · edited Feb 23 '15 at 21:58

1

If you don't need to check a time you can even do the following:

SELECT * 
FROM completed_games 
WHERE `date` BETWEEN '2015-02-22' AND '2015-02-28'

Tip: Try to use backticks (`) around keywords or special chars.

edited Feb 23 '15 at 21:58

mbomb007

3,788
3
39
68

answered Feb 23 '15 at 15:05

Grecool

58
1
3

1

This is, in my opinion, the best answer. – mbomb007 Feb 23 '15 at 15:10
I think this a question about MySQL. – Strawberry Feb 23 '15 at 15:39
MySQL uses backticks instead of square brackets. – mbomb007 Feb 23 '15 at 21:30

What's wrong with this SQL syntax to select data between two dates?

3 Answers3