CODEWITHSUNDEEP
mysqldatedifferencedays
1337
1337

Reputation: 51

mysql: slightest difference between days

i want to write a query which shows me the slightest difference between a given day of a month and the days in the tables.

select * from students where 5 = month(birthdate)

I want to search for the students who were born in May and now i want to get the slightest difference between a given day and the day of the birthday.

For example:

Alan 1980-05-03 Bob 1978-05-07

And i set the day to 8. The result should show me Bob. How should the query look like?

Upvotes: 2

Views: 36

Answers (2)

EoinS
EoinS

Reputation: 5482

Here you set the day, you choose all students with birthday that matches the birth date closest to the day you chose.

Set @Day = 5; -- your number
Select * from students 
where month (birthdate)=5 -- set this to month
Having birthdate = (select min(birthdate) from students order by abs(@Day - dayofmonth(birth date)) desc limit 1);

Dayofmonth() returns the day (1-31) of the month for a given date.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You could use:

SELECT *
FROM students
WHERE month(birthdate) = 5
ORDER BY ABS(DAY(NOW()) - DAY(birthdate))
LIMIT 1;

SqlFiddleDemo

When you compare only in one month range you could easily get difference between day in particular month.

Note: This won't handle ties.

Upvotes: 1

Related Questions