Write a query to see the "date" in table is still to come or has passed by?
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开发者_C百科 great.
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;
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.
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
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
.
精彩评论