CODEWITHSUNDEEP
phpsqldate
John123
John123

Reputation: 31

SQL and date comparison

I have a sql statement, i select a few items, but I need to make sure that the date i have saved is greater then the current date.

SELECT * FROM table WHERE column_date > current_date

How do I make the code over, working?

Column date is saved like this 0000-00-00 00:00:00 (usual save, in other words).

This is what I try to achieve: AND time > current_date OR time == NULL But it ain't working. The time column is currently 0000-00-00 00:00:00 which is NULL, right?

Or how can I do, it must be greater than or equal to 0000-00-00 00:00:00

Upvotes: 2

Views: 270

Answers (3)

Andriy M
Andriy M

Reputation: 77737

As per this documentation article, 0000-00-00 is a "dummy date" that can be used instead of NULL, which means 0000-00-00 itself is not NULL.

Given that 0000-00-00 cannot be greater than any other date, you should use either a condition with OR:

SELECT * FROM table WHERE column_date > NOW() OR column_date = '0000-00-00'

or a union:

SELECT * FROM table WHERE column_date > NOW()
UNION ALL
SELECT * FROM table WHERE column_date = '0000-00-00'

Alternatively you could use a construct like this:

SELECT *
FROM table
WHERE IFNULL(NULLIF(column_name, '0000-00-00'), '9999-12-31') > NOW()

But that would probably prohibit the query from taking advantage of the index on column_date, if any.

Upvotes: 0

Dennefyren
Dennefyren

Reputation: 344

You could use the Mysql Function NOW():

SELECT * FROM table WHERE column_date > NOW()

Upvotes: 1

Nirmal
Nirmal

Reputation: 9549

If you are using MySQL, NOW() should do the trick.

SELECT * FROM table WHERE column_date > NOW()

If you want to eliminate the time value and just compare to date value, following could be used:

SELECT * FROM table WHERE column_date > CURDATE()

Upvotes: 2

Related Questions