2

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
1
  • Please Edit the Question to specify the storage format of the data. ISO SQL will not work with most data formats (only enterprise geodatabase, really), so requesting SQL might not provide you a solution.
    – Vince
    Jun 30 at 12:29

1 Answer 1

3

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
;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.