获取与当前日期最接近的日期sql [英] Get closest date to current date sql

问题描述

我知道可能是此的重复项，但是当我尝试他们提供的答案时，我得到了不同的答案...

i know that may question is a duplicate of this but when i tried the answers they provide i got a different answer...

所以我有一张桌子，今天是 2017年6月2日 tblpres

so i have a table and today is 06/02/2017 tblpres

patID     pres       presDate
  1     |    asd     |    05/29/2017
  2     |    qwe     |    05/20/2017
  3     |    zxc     |    06/01/2017
  4     |    ety     |    05/27/2017
  5     |    ttt     |    05/18/2017

所以我进行了查询以获取与当前日期最接近的日期，但我却获得了ID 3，而ID是3与当前日期最接近的日期却得到了一个差异答案.

so i made a query to get the closest date to the current date but instead i get id 3 whch is the closest date to the current date i get a diff answer..

SELECT pres,presDate
FROM tblpres
WHERE patID = '20150518384'
ORDER BY presDate DESC
LIMIT 1

SELECT pres, presDate
FROM tblpres
WHERE patID = '20150518384'
ORDER BY ABS( DATEDIFF(presDate, `06/02/2017` ) )
LIMIT 1

SELECT pres, presDate
from tblpres
WHERE patID = '20150518384'
order by abs('06/02/2017' - presDate) desc
limit 1

SELECT pres,presDate
FROM tblpres
WHERE `patID` = '20150518384' AND presDate < '06/02/2017'
ORDER BY presDate
LIMIT 1

这是我尝试过的查询，但似乎无济于事...我的确得到了该查询的答案，但结果却不是我期望的..

this is the query i tried but nothing seems to work... i do get an answer for this query but the result is not what i expected..

推荐答案

使用ISO/ANSI标准日期格式:

Use ISO/ANSI standard date formats:

SELECT pres, presDate
FROM tblpres
WHERE patID = '20150518384'
ORDER BY ABS( DATEDIFF(presDate, '2017-06-02') )
LIMIT 1;

或者，更好的是，使用 CURDATE():

Or, better yet, use CURDATE():

SELECT pres, presDate
FROM tblpres
WHERE patID = '20150518384'
ORDER BY ABS( DATEDIFF(presDate, CURDATE()) )
LIMIT 1;

如果 presdate 实际上不是日期，则可能需要将其转换为一个日期:

If presdate is not actually a date, you might need to convert it to one:

SELECT pres, presDate
FROM tblpres
WHERE patID = '20150518384'
ORDER BY ABS( DATEDIFF(str_to_date(presDate, '%m/%d/%Y'), CURDATE()) )
LIMIT 1;

这篇关于获取与当前日期最接近的日期sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！