Table USER
:
ID | NAME | DATE | TIME |
---|---|---|---|
1 | ALPHI | 2000-09-09 | 08:29:34 |
2 | DANE | 2000-09-09 | 07:01:02 |
3 | SAM | 2000-09-09 | 06:02:03 |
4 | ALI | 2000-06-01 | 11:11:11 |
To fetch the the one record, which has the date & time just before the 2000-09-09 & 08:29:34.
SELECT
DATE, TIME
FROM
USER
WHERE
DATE > 2000-09-09
AND TIME > 08:29:34
ORDER BY
DATE, TIME
FETCH FIRST 1 ROW ONLY
But this query doesn't seem correct.
I need to get the result as
2 | DANE |2000-09-09 | 07:01:02
It can be any date. Had to fetch the latest before the given date and time
Using a limit query should work here, but your your current date logic is slightly off. Try using this version:
SELECT DATE, TIME
FROM USER
WHERE
(DATE = '2000-09-09' AND TIME < '08:29:34') OR DATE < '2000-09-09'
ORDER BY DATE DESC, TIME DESC
FETCH FIRST 1 ROW ONLY;
The WHERE
clause restricts to only records on or before Sep 9th 2000 at 8:29am. From that subset, we then order descending by date/time and retain only the first record.