sqldb2

To get the previous record which is less than the given date and time


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


Solution

  • 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.