Find Closest Date in SQL Server

Find closest date in SQL Server

The where clause will match all rows with date less than @CurrentDate and, since they are ordered descendantly, the TOP 1 will be the closest date to the current date.

SELECT TOP 1 *
FROM x
WHERE x.date < @CurrentDate
ORDER BY x.date DESC

SQL server: Get record with date closest to given date

One method is:

SELECT TOP 1 s.*
FROM studies s
ORDER BY ABS(DATEDIFF(day, s.studydate, @givendate));

This uses DATEDIFF() to get the closest date. Note that this is using day for the difference. If your "dates" have a time component, you might want a different date part.

Note that this will not take advantage of indexes. A faster method (if you have the indexes) is a bit more complicated:

SELECT TOP (1) s.*
FROM ((SELECT TOP 1 s.*
       FROM studies s
       WHERE s.studydate <= @givendate
       ORDER BY s.studydate DESC
      ) UNION ALL
      (SELECT TOP 1 s.*
       FROM studies s
       WHERE s.studydate > @givendate
       ORDER BY s.studydate ASC
      )
     ) s
ORDER BY DATEDIFF(day, s.studydate, @givendate));

Although this is more complicated, each subquery can use an index on studydate. The final sort would have only two rows, so it should be really fast.

Select closest date to another date

SELECT TOP 1
    cp.CONTACTNO, cp.AGEONENTRY, cp.COURSETITLE, cp.FACULTY, ad.POSTCODE,
FROM 
    COURSEPLACE cp
    INNER JOIN ADDRESS ad ON ad.CONTACTNO=cp.CONTACTNO
WHERE CP.TYPE = 'Application'
    AND CP.TERM = '2016/5'
    AND AD.TYPE = 'Home'
ORDER BY 
    DATEDIFF(AD.CREATIONDATE, CP.CREATIONDATE) ASC;

SQL Server: Find records with closest Date to CurrentDate based on conditions

create table #temp(did varchar(10),pid int,startdate datetime,enddate datetime)

insert into #temp values('A1',01,'2014-01-08','2018-01-08')
insert into #temp values('A2',01,  '2016-11-23'  ,   '2016-12-01'   )
insert into #temp values('A3',01, '2016-12-03'  ,   '2016-12-08'  )
insert into #temp values('A4',02,  '2016-10-10'  ,   '2016-12-31'  )
insert into #temp values('A5',02, '2016-12-01'  ,   '2016-12-05'  )


select b.pid,b.startdate,b.enddate
from
(
select ROW_NUMBER()over(partition by pid order by id desc) as SID , a.*
from
(
select 
ROW_NUMBER()over(partition by pid order by startdate,enddate desc) as ID
, * from #temp 
--to identify whether it is inner or outer
--1 means outer
--2 means inner
)a
where '2016-12-02' between startdate and enddate
--to find date lies in outer or inner range and select the required
)b
where b.SID=1

Get closest date to current date sql

Use ISO/ANSI standard date formats:

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

Or, better yet, use CURDATE():

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

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 query to return closest date match for each Id

Depending on what your definition of "closest time" is, this is one way which will return one record for each ID where the LoadTime and Updated is closest to each other, which would essentially be the first record that was updated.

;with cte as(
SELECT [Id]
      ,[Updated]
      ,[LoadTime]
      ,[Field4]
      ,[Field5]
      ,[Field6]
      ,ClosestTime = datediff(second,LoadTime, Updated)
  FROM [dbo].[tblTempData]),

select
    [Id]
    ,[Updated]
    ,[LoadTime]
    ,[Field4]
    ,[Field5]
    ,[Field6]   
from
    cte
inner join
    (select Id, min(ClosestTime) dt
     from cte
     group by Id) cte2 on cte.Id = cte2.Id and cte2.dt = cte.ClosestTime

Another way is using row_number()

;with cte as(
SELECT [Id]
        ,[Updated]
        ,[LoadTime]
        ,[Field4]
        ,[Field5]
        ,[Field6]
        ,RN = row_number() over (partition by Id order by LoadTime, Updated)
FROM [dbo].[tblTempData])

select 
    [Id]
    ,[Updated]
    ,[LoadTime]
    ,[Field4]
    ,[Field5]
    ,[Field6]
from
    cte
where
    RN = 1

Query item with closest date based on current date

You can try to use ROW_NUMBER window function to make it.

SELECT ITEM_NO,
       REQUIRED_DATE,
       PRICE
FROM (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY ITEM_NO ORDER BY REQUIRED_DATE) rn
    FROM DATA_WAREHOUSE].[app].[OHCMS_HOPS_ORDERS]
    where REQUIRED_DATE >= GETDATE() and PRICE is not null
)t1
WHERE rn = 1

---

Related Topics