TSQL - Retrieve the row where the date is the nearest of my value

Question

I'm currently writing a TSQL stored proc who need to retrieve a row who is the nearest of my datetime passed as parameter.

Here is the current code :

DECLARE @IdChannel Int
DECLARE @ExactTime Datetime

SET @IdChannel = 1
SET @ExactTime = '2016-01-01 13:31:49.147'

SELECT TOP 1    
    DER.[DER_DTMODIF]
FROM        
    [SGPI]..[DER_DECRO_REGIE] DER
INNER JOIN  
    SGPI..MAI_MACHINE_INPUT MAI ON MAI.MAI_ACTIVE_INPUT = DER.DER_INPUT 
                                AND MAI.MAI_MACHINE_ID = DER.DER_MACHINE_ID
WHERE       
    (@IdChannel IS NULL OR MAI.MAI_RELATIVE_CHA_ID = @IdChannel)
    AND [DER_DTMODIF] /*is the nearest of @ExactTime*/

Here is a snapshot of my data. The request must return 2016/01/01 14:00:17.733 :

I haven't find this post. Do you want me to close this one ? — Xavier W., Apr 05 '16 at 15:39

score 1 · Answer 1 · answered Apr 05 '16 at 15:37

1

You have top 1 in your query but you have no order by. Without an order by you have no way to determine which row will be returned.

How about top 1 and something along these lines.

order by ABS(DATEDIFF(second, @ExactTime, DER_DTMODIF))

answered Apr 05 '16 at 15:37

Sean Lange

33,028
3
25
40

score 1 · Answer 2 · answered Apr 05 '16 at 15:37

Use ORDER BY and TOP:

SELECT TOP 1 DER.[DER_DTMODIF]
FROM SGPI..[DER_DECRO_REGIE] DER INNER JOIN
     SGPI..MAI_MACHINE_INPUT MAI
     ON MAI.MAI_ACTIVE_INPUT = DER.DER_INPUT AND
        MAI.MAI_MACHINE_ID = DER.DER_MACHINE_ID
WHERE (@IdChannel IS NULL OR MAI.MAI_RELATIVE_CHA_ID = @IdChannel)
ORDER BY ABS(DATEDIFF(ms, DER_DTMODIF, @ExactTime));

Note: If you have a lot of records that match the WHERE clause, then this could be inefficient. If you know that you'll have matching times every day, you might want to change the WHERE to something like:

WHERE (@IdChannel IS NULL OR MAI.MAI_RELATIVE_CHA_ID = @IdChannel) AND
      ABS(DATEDIFF(ms, DER_DTMODIF, @ExactTime)) < 24*60*60*1000

(This is just an example for one day.)

This would reduce the volume of data used for the ORDER BY, which can increase performance.

TSQL - Retrieve the row where the date is the nearest of my value

2 Answers2