The Naked Technologist

I have been recently working on a trip planner application where I have had to display the next trip that a user has coming up. This involved having to make a comparison between the starting date of the trip and the current date to find the trip that met this criteria. I thought I would share the basic query of how you can do this should anyone need to do accomplish a similar task.

Given the Following:
- My table name is trip_list
- The starting date of the trip (which we will compare) is named date_from
- The dates stored into the date_from field are stored in the format of dd-mm-yyyy

SELECT * FROM trip_list WHERE trip_list.date_from =
(SELECT MIN(trip_list.date_from) FROM trip_list WHERE
trip_list.date_from >= DATE_FORMAT(NOW(),'%d-%m-%Y'))

What I am doing here is selecting all columns in the table where the starting date is equal to the lowest date that is after the current date. At the end of the query I format the output of the current date to be dd-mm-yyyy for comparison since this is an application for the Australia / New Zealand region and that’s their date structure.

That’s all there is to it.

- Jonathan LeBlanc

In: SQL | tags: mysql, Programming, SQL. | #