CODEWITHSUNDEEP
mysqldate
emil.c
emil.c

Reputation: 2018

Return data from mysql with date later than now

Here's table I've got:

projects(p_id,p_name,p_deadline)

p_deadline is defined in MySQL as DATE.

How to write a query that returns only projects with p_deadline date that is newer than NOW()? I don't want to display projects that passed deadline date.

Upvotes: 1

Views: 1434

Answers (3)

Leigh
Leigh

Reputation: 28873

(Just to clarify one point ..)

But how do I get data even from day equal to the NOW();? I tried >= but it doesn't seem to work, I guess because NOW(); returns even the time

Correct. DATE values have a time of midnight. Since now() returns both a date and time the two values will rarely match. Only at midnight. So the actual comparison translates to something like this:

/* notice the dates match, but the current time is later */
WHERE '2012-02-17 00:00:00' >= '2012-02-17 13:35:48'

Instead you should use curDate(). It returns a date (only).

WHERE p_deadline >= CURDATE();

Upvotes: 0

Pulkit Goyal
Pulkit Goyal

Reputation: 5674

SELECT * FROM projects WHERE p_dealine > NOW()

Upvotes: 2

Eugen Rieck
Eugen Rieck

Reputation: 65342

SELECT
  ... whatever ...
FROM
  ... whatever ...
WHERE
  ... whatever ...
  AND projects.p_deadline>NOW();

Upvotes: 3

Related Questions