Write a query to see the "date" in table is still to come or has passed by?

Question

I want to write a sql query to find the rows with dates that are still to come.

I was trying something like this:

$now_time = time();
$queryEvents = "SELECT summary, startdate 
       FROM c7_events AND startdate > $now_time
       WHERE users_id = $users_id";

but this is not working because the startdate in table has data like: 2010-01-22 11:49:55

I don't care about the time, just the date. But if time can be done easily too, that would be great.

Answer 1

If you use oracle:

select summary, startdate 
from c7_events
where startdate > systimestamp(1)
and users_id = $users_id

Note: the example query you give will fail because you have the AND and WHERE out of order. IE is should read:

SELECT summary, startdate 
   FROM c7_events WHERE startdate > $now_time
   AND users_id = $users_id

Answer 2

For SQL Server:

SELECT summary, startdate 
FROM c7_events 
WHERE dateadd(dd,0,datediff(dd,0,startdate)) >
      dateadd(dd,0,datediff(dd,0,$now_time))
AND users_id = $users_id

You could also just use the GetDate() function instead of passing in $now_time.

Answer 3

Use:

SELECT t.summary, 
       t.startdate 
  FROM c7_events t
 WHERE t.startdate > CURRENT_TIMESTAMP
   AND t.users_id = $users_id

CURRENT_TIMESTAMP is ANSI standard, supported on MySQL, SQL Server, Oracle (9i+), and likely Postgres.

Answer 4

Whatever database you're using, I would be surprised to find that there was not a NOW() function or similar. In mysql you can just say

SELECT summary, startdate  
FROM c7_events AND startdate > NOW()
WHERE users_id = $users_id;