CODEWITHSUNDEEP
phpmysqlsqldateselect
Elomar Adam
Elomar Adam

Reputation: 251

Select date with the least different (from today) from database

Can someone tell me how to select the most recent date (with the least different) from now?
Currently I'm using something like this

SELECT MAX(DatumAangemaakt) AS RecenteSchema FROM Personeelsschema

I use the Max() function, but it's no good because if I have a date in the future (e.g. 2015-06-23) this date will be selected.

And what I need is to select today's date (if there any) or the most recent date from today, e.g:
2015-06-23
2015-06-01
2015-05-17 (this one will be selected)

Is there a way to do that?

Upvotes: 1

Views: 57

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

Get all dates past the current date, order them and get the top record:

SELECT DatumAangemaakt AS RecenteSchema
FROM Personeelsschema
WHERE DATE(DatumAangemaakt) >= DATE(NOW()) 
ORDER BY  DatumAangemaakt  LIMIT 1

SQL Fiddle Demo

If you also want to include dates from the past in your proximity check, then you can use the following query:

SELECT DatumAangemaakt
FROM Personeelsschema
ORDER BY ABS(TIMESTAMPDIFF(DAY, DatumAangemaakt, DATE(NOW()))) LIMIT 1

SQL Fiddle Demo

Upvotes: 2

Related Questions