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
MazdajaiAsked:
Who is Participating?
 
tel2Commented:
Good.  Please accept my answer and open another question to cover your new problem.

Thanks Mazdajai
0
 
tel2Commented:
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?
0
 
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
0
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.

 
tel2Commented:
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?
0
 
MazdajaiAuthor Commented:
It is somehow related because I am trying to use it in a sub query.
0
 
tel2Commented:
What is the answer to Q1, please Mazdajai.
0
 
MazdajaiAuthor Commented:
Q1 - yes
0
 
tel2Commented:
Thanks for the points.
0
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.