SQL Nearest Date

Question

I need to get a date such as '2010-04-27' as a string in php and find the nearest 5 dates in a table. The date in the table are saved as a date type.

This massive SQL, rubbish, and "SELECT * FROM times WHERE date <= date('.$date.') LIMIT 0,3 ORDER BY date ASC" — Will, Aug 30 '10 at 10:55

score 3 · Answer 1 · answered Aug 30 '10 at 10:57

3

you can use DATEDIFF + ABS

SELECT ABS(DATEDIFF(myfield,'2010-04-27')) AS diff FROM mytable ORDER BY diff LIMIT 5;

answered Aug 30 '10 at 10:57

Haim Evgi

Im getting 'is not a valid resource' – Will Aug 30 '10 at 11:00
check this for "is not a valid resource": http://stackoverflow.com/questions/253378/not-a-valid-mysql-resource – Adam Butler Aug 30 '10 at 11:12
This doesnt appear to be filling $row. while($row = mysql_fetch_array($result)) { The array row contains no column data – Will Aug 30 '10 at 11:28

score 2 · Answer 2 · answered Aug 30 '10 at 10:58

2

you could also query the difference eg. something like

abs(datediff(date, $date))

then order by this

answered Aug 30 '10 at 10:58

Adam Butler

2 Answers2