SQL - Find date closest to current date

Question

I'm having some trouble with my SQL query. I got this table:

insert into Table1 (date, personssn)
insert ('2012-01-21 12:01:33', '123456789');
insert into Table1 (date, personssn)
insert ('2012-02-22 12:01:33', '123456789');

The problem is that I want to select the personssn who have a date CLOSEST to the current date. I've been working with "CURDATE()" but can't seem to get it to work. Anyone that can help me in the right direction?

Thanks.

Answer 1

select  *
from    Table1
order by
        abs(now() - date) desc
limit   1

Answer 2

Use datediff. It returns the Difference between two dates. However, you can have dates before and after today in your database. That's why you need ABS() without it the smallest value would be selected first, but we want the value closest to 0. IE a Date-Difference of 3 is “bigger” than -250, but 3 days off is closer. That is why you use the absolute value.

SELECT t1.date, t1.personssn 
  FROM Table1 AS t1
 ORDER BY ABS(DATEDIFF(t1.date, NOW())) ASC
 LIMIT 5

Answer 3

Try this with simple order by :

SELECT date,personssn FROM Table1 ORDER BY date desc