SQL: Comparing multiple dates I got a table with multiple columns that contain DateTime namely gendate1 up to gendate8. In my stored procedure, the user will have to input a date (@date_start). Now I have to compare the inputted date to the 8 dates which will filter all the data. The problem is that I have to get the two closest dates (out of eight dates) to the given date. Sorry I'm just a beginner in sql.

In response to sir Akina

I don't have the actual code yet because it confuses me of what to do first. 


Should I make a temporary table, loop the result of the query and filter each of the result (but how to get the 2 closest dates out of eight dates) then insert it to the temporary table? 
or should I put all the condition under the where clause?


Example

 gendate1 = '1/6/2019'
,gendate2 = '4/6/2019'
,gendate3 = '2/6/2019'
,gendate4 = '3/6/2019'
,gendate5 = '5/6/2019'
,gendate6 = '7/6/2019'
,gendate7 = '8/6/2019'
,gendate8 = '10/6/2019'

@date_start = '1/30/2019'


How to get the gendate1 and gendate3? (they are the closest to @date_start)
A slightly different and much more efficient approach (single CIX scan instead of SIXTEEN CIX scans). Assuming this table:

CREATE TABLE dbo.tablename
(
  id int PRIMARY KEY,
  gendate1 datetime,  gendate2 datetime,  gendate3 datetime,  gendate4 datetime,
  gendate5 datetime,  gendate6 datetime,  gendate7 datetime,  gendate8 datetime
);


Then we can use this query to unpivot:

DECLARE @date_start datetime = '20190130';

;WITH x AS
(
  SELECT tp.id, tp.d FROM dbo.tablename AS t
  UNPIVOT 
  (
    d FOR ds IN 
    (
      t.gendate1,t.gendate2,t.gendate3,t.gendate4,
      t.gendate5,t.gendate6,t.gendate7,t.gendate8
    )
  ) AS tp
)
SELECT id, 
  next_date = MIN(CASE WHEN d &gt; @date_start THEN d END),
  prev_date = MAX(CASE WHEN d &lt; @date_start THEN d END)
FROM x
GROUP BY id;


If you want a tie when @date_start is a direct hit, pick one to change to &gt;= or &lt;= (just don't do that to both).

Results with a single id given the sample data in the question:


  


Plan here is much nicer: