CODEWITHSUNDEEP
mysqlsql
Erik Åstrand
Erik Åstrand

Reputation: 379

SQL - Find date closest to current date

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.

Upvotes: 6

Views: 10510

Answers (5)

Deniyal Tandel
Deniyal Tandel

Reputation: 632

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

Upvotes: 6

xQbert
xQbert

Reputation: 35323

Something like:

with myCTE as (
SELECT to_date('21-Jan-2012 12:00') as mydate, '123456789' as myId FROM DUAL UNION
Select to_date('22-Feb-2012 12:00') as mydate, '123456789' FROM DUAL )
Select abs(sysdate-mydate) as diff, mydate, myID from myCTE order by diff

but this is oracle.. I'm not sure of the mysql text.

Upvotes: 1

Anvesh
Anvesh

Reputation: 7683

Try this with simple order by :

SELECT date,personssn FROM Table1 ORDER BY date desc

Upvotes: 1

Avi
Avi

Reputation: 1145

Use MAX(date),If u want only one row as output else use date <= sysdate otherwise how will u define closet to sysdate..

Upvotes: 0

Andomar
Andomar

Reputation: 238048

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

Upvotes: 13

Related Questions