2

I am using MySQL to filter the date according to the CreationDate.

If I did this without comparing the CreationDate, it will return some results.

    use todo;
    SELECT *
    FROM ToDoItem
    WHERE ToDoListID = 1
    AND COMPLETED = 1
    AND Priority>=2;

The result

However, when I add the comparison with date, there will be no result.

    use todo;
    SELECT *
    FROM ToDoItem
    WHERE ToDoListID = 1
    AND COMPLETED = 1
    AND Priority>=2
    AND CreationDate > ´2004-11-18 15:26:58´;

No Return

It says there is a "SQL syntax error near '15:26:58' " and the "query interrupted".

I do not why this does not work because I saw some examples on stackoverflow using ">" and "<" to compare two date directly.

Any help would be appreciated. Thank you.

Kevin
  • 197
  • 2
  • 11

2 Answers2

9

Use single quotes (') instead of backticks (`), so your query should be:

use todo;
    SELECT *
    FROM ToDoItem
    WHERE ToDoListID = 1
    AND COMPLETED = 1
    AND Priority>=2
    AND CreationDate > '2004-11-18 15:26:58';
Piskvor left the building
  • 91,498
  • 46
  • 177
  • 222
Gouda Elalfy
  • 6,888
  • 1
  • 26
  • 38
  • Great! Thank you very much! This solves my problem. I am using the VirtualBox and I did not notice this. – Kevin Jan 07 '16 at 16:33
0

To get reliable results, you should use DATEDIFF():

SELECT *
  FROM ToDoItem
  WHERE ToDoListID = 1
    AND COMPLETED = 1
    AND Priority>=2
    AND DATEDIFF(CreationDate, '2004-11-18 15:26:58') > 0;

DATEDIFF(Expr1, Expr2) returns >0 if Expr1 is after Expr2, and <0 if Expr1 is before Expr2.

Darwin von Corax
  • 5,201
  • 3
  • 17
  • 28