[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 787
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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