abramlimpin abramlimpin - 24 days ago 7
SQL Question

Display latest or upcoming event record

I have an events table:

EventID Name StartDate
1 Alpha 2016-07-01
2 Bravo 2016-11-01
3 Charlie 2017-03-01
4 Delta 2017-06-01
5 Echo 2017-09-01


I would like to display the upcoming event from the current date using this SQL statement:

SELECT TOP 1 * FROM Events WHERE StartDate < GETDATE() ORDER BY StartDate DESC


but it returns Event #2 (it should be #3). Am I missing something?

Answer

I think your logic is wrong:

SELECT TOP 1 e.*
FROM Events e
WHERE e.StartDate > GETDATE()
ORDER BY StartDate ASC;

This gets the next event. Your logic gets the previous event.

Comments