Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 794
  • Last Modified:

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?
0
Mazdajai
Asked:
Mazdajai
  • 5
  • 3
1 Solution
 
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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

Thanks Mazdajai
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now