How do you get the next upcoming date from a table?

Question

I am currently working on building a baseball website. On one side of the page I want to display information on the next upcoming game. I am using mysql ver. 5.0.45. My table is built as so:

opposingTeam, date, time, score

• Date is in the mysql date format (yyyy-mm-dd)
• I have found the datediff(date1,date2) command and tried experimenting with this with no luck

I have tried queries such as:

SELECT *
FROM schedule
WHERE MIN(DATEDIFF(CURDATE(),date))

• But this gives me an arror because I am using the MIN() function wrong
• I am still pretty new to mysql and I just can not seem to figure this out

Answer 1

I think it'd be something like this:

SELECT * FROM `schedule` WHERE `date` > NOW() ORDER BY `date` LIMIT 1

The LIMIT 1 limits the query to returning one row, but if you need more than one, you can adjust the number accordingly (or omit LIMIT 1 entirely, to fetch data for all future games)

Answer 2

maybe

select top 1 * from schedule where date>=CURDATE() order by date

Answer 3

Hi,

If you want to display the next game (including today) you can use the following:

SELECT * FROM `schedule` WHERE `date` >= CURDATE() ORDER BY `date` LIMIT 1;

If you want to display all upcoming games use:

SELECT * FROM `schedule` WHERE `date` >= CURDATE() ORDER BY `date`;

Answer 4

A couple things to consider.

You are likely consigning yourself to eternal torment if you name a column "date".

The query optimizer is notoriously inept at optimizing queries that compare expressions and/or functions with values - try to use only column names.

Hence you should compare the value of the date column with, say, CURDATE(). (It's ok to compare a column value with an expression or function.)