SQL select the two values that bounds an input value

Question

I have a table with date fields. I need to select two rows where the first row is the date just before an input date and the second row is the date just after the input date.

For example, given an input date of "10/06/2022" and the following date table:

The id is an Integer and date is a Date field. The table is an ArcGIS geodatabase table.

id	date
1	02/06/2022
2	05/06/2022
3	08/06/2022
4	11/06/2022
5	16/06/2022
6	18/06/2022
7	20/06/2022

I need the SQL to return two rows:

id	date
3	08/06/2022
4	11/06/2022

Answer 1

In SQL, something like this might work, I assume that your geodatabase is installed on a SQL Server :

SELECT TOP 1 id, date FROM date_table
WHERE date < CAST('10/06/2022' as date)
ORDER BY date DESC
UNION ALL
SELECT TOP 1 id, date FROM date_table
WHERE date > CAST('10/06/2022' as date)
ORDER BY date
;