Expired articles appear after expiration date - help

[ppowell]

I have a table, "article" that has a datetime field "article_expires". In my SQL statement I have this:

SELECT id, title, body
FROM article
WHERE (article_expires IS NOT NULL OR article_expires = '' OR article_expires LIKE '0000-00-00%' OR article_expires < now())
ORDER BY ranking DESC, record_entered DESC, upper(title) ASC, upper(body) ASC

Here is info in the table for one row with an expiration date:

----+--------------------------------------------------------------------------+---------------------+
| id | title | article_expires |
+----+--------------------------------------------------------------------------+---------------------+
| 1 | This article should never appear | 2006-06-12 14:55:36 |

Yet in my resultset this article appears even as of 3:04pm! What am I possibly doing wrong for this to happen and how can I fix this? The goal is to only show unexpired articles AND/OR articles that have no expiration date.

Thanx
Phil

[TobyRT]

I think you might need to use the curdate() function of SQL to make this work in the current syntax you're using. Personally I find it easier to put the date in an easier to use format using DATE_FORMAT().

I'm not sure why you're wrapping your WHERE clause in brackets though?

I use something like this for exactly the same function (except that I only go as far as date, not time, and I'm including today's date in the query - tomorrow it will be gone 🙂:

SELECT id,
title,
DATE_FORMAT(dateadded,"%d %M %Y") AS dateadded,
expiry,
article
FROM news
WHERE
DATE_FORMAT(expiry,"%Y-%m-%d") >= DATE_FORMAT(NOW(),"%Y-%m-%d")
OR active = "1"
ORDER BY id DESC')
or die ("Error in Query: $query.".mysql_error());

[ppowell]

Oh I wrap it in parentheses because there are other criteria for article inclusion; I am just using the article_expires part because that's the only part that fails right now

I'll try curdate()

Phil

[ppowell]

it works! But I don't understand why.. why does curdate() work and now() fail?

Phil