CODEWITHSUNDEEP
mysqlsql
imran
imran

Reputation: 179

Check if a date is upcoming or past in MySQL

I have two columns in a table

Now I want to write a query which should returns row which has time and date in future or past.

I wrote something like this

SELECT *
FROM events e
WHERE CONCAT(e.event_date, ' ', e.event_time) <= DATE_FORMAT(NOW(), '%m/%d/%Y  %g:%i %a')

But it didn't work, it's just one of those things which you never know what's wrong with them

Thanks in advance!

Upvotes: 14

Views: 29253

Answers (3)

Curt
Curt

Reputation: 5722

SELECT * 
FROM Events
WHERE event_date < CURRENT_DATE()
      OR (event_date = CURRENT_DATE() AND event_time <= CURRENT_TIME())

This has the advantage of using any index that might exist on event_date.

Upvotes: 14

DevlshOne
DevlshOne

Reputation: 8457

select * from events e where TIMESTAMP(e.event_date,e.event_time) <= NOW()

Upvotes: 4

peterm
peterm

Reputation: 92785

A possible solution if values of event_date and event_time are stored as you showed them

Select past events

SELECT *
  FROM events
 WHERE STR_TO_DATE(CONCAT(event_date, ' ', event_time), '%m/%d/%Y %h:%i %p') < NOW()

Select future events

SELECT *
  FROM events
 WHERE STR_TO_DATE(CONCAT(event_date, ' ', event_time), '%m/%d/%Y %h:%i %p') > NOW()

Here is SQLFiddle demo

Upvotes: 0

Related Questions