[RESOLVED] Query won't filter by date field

[retro]

I must be doing something glaringly obvious here, but I am just starting with MySQL and can't work this out!

I have a table, orders, and two DATE fields in it that are used to filter out holidays, holdstart and holdend. These are both DATE fields (not DATETIME), notnull. As such, they default to 0000-00-00.

I want to find customers who are on hold - that is customers who have a value in holdstart and holdend other than 0000-00-00.

I tried using this query:

SELECT *
FROM orders
ORDER BY holdstart
WHERE holdstart > '0000-00-00'

...but this throws back an error. Through phpMyAdmin, the error is:

#1064 - 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 'WHERE holdstart > '0000-00-00'
LIMIT 0, 30' at line 1

If I take out the WHERE statement, the query works fine. Obviously something is wrong there. I have tried different values, such as holdstart = '0000-00-00' or holdstart = holdend, but still get an error.

What am I doing wrong?

[bretticus]

Please note the order of your WHERE clause in the statement. WHERE always comes before ORDER BY. Thus, when you remove the WHERE clause in your example, you have a complete SQL statement, when you append the WHERE clause (after the ORDER BY clause) you get a complete statement with an incomplete clause. Thus, the syntax error in your SQL statement.

Example: Your SQL statement should be:

SELECT *
FROM orders
WHERE holdstart > '0000-00-00'
ORDER BY holdstart 

Notice the subtle change.

The MySQL manual shows the order of clauses in a SELECT statement.

[retro]

Ahh, that's great... I knew it would be something simple!

You learn something new every day 🙂

Thanks very much!