SQL return the closest matching date

I have a date field that I want SQL to query in a optimized fashion and return the closest match, any suggestion?
LVL 21
Who is Participating?
Good.  Please accept my answer and open another question to cover your new problem.

Thanks Mazdajai
Hi Mazdajai,

Are you saying you have a date field in a table and you want to find the record which contains the date which is closest to a date supplied by the user?

If so, how about you:
- subtract one date field from the other,
- sort the absolute value of those differences,
- and pick the lowest one (using "LIMIT 1").

Or do I misunderstand your requirements?
MazdajaiAuthor Commented:
Yes, I solved it with LIMIT 1, but I am getting an error with 'mybirthdate does not exist' when I try to use it in the sub query:

with mybirthdate as (select birthdate from table1 t1
where birthdate >= '1960-01-01' and id <= 1000
order by birthdate
limit 1)
select *
from table2 t2
join t1 on t1.id = t2.id
where t2.birthdate = mybirthdate

ERROR: column "mybirthdate" does not exist
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Q1. So are you saying you've already solved the problem outlined in your original question?

Q2. How is the above SQL query related to your original question?
MazdajaiAuthor Commented:
It is somehow related because I am trying to use it in a sub query.
What is the answer to Q1, please Mazdajai.
MazdajaiAuthor Commented:
Q1 - yes
Thanks for the points.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.