CODEWITHSUNDEEP
db2
max092012
max092012

Reputation: 407

Fetch records created within 24 hours in DB2

I need to fetch the records created within 24 hours . I wrote the below query however its not giving the desired result. 

SELECT a,b,enddate,status 
FROM data WHERE a='1013'AND c ='1250'and (TIMESTAMPDIFF(8,char(timestamp(enddate)- 
TIMESTAMP(CURRENT_DATE)))) between 0 and 24

Below is the data present in the table 

 A     B      C       Enddate
1013  Test1  1250    28-March-2020 11:00 AM 
1013  Test2  1000    28-March-2020 15:00 PM
1013  Test3  1250    29-March-2020 05:00 AM
1013  Test4  1250    29-March-2020 13:00 PM 
1013  Test5  2500    29-March-2020 17:00 PM
1013  Test6  1250    31-March-2020 19:00 PM

Assuming that CURRENT_DATE = 29-March-2020 19:00 PM the query should return 2 rows Test3 and Test4 . The above query does not return any row .

Upvotes: 0

Views: 851

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12339

SELECT B, TS 
FROM 
(
VALUES 
  ('Test1', TIMESTAMP('2020-03-28-11.00.00'))
, ('Test2', TIMESTAMP('2020-03-28-15.00.00'))
, ('Test3', TIMESTAMP('2020-03-29-05.00.00'))
, ('Test4', TIMESTAMP('2020-03-29-13.00.00'))
, ('Test5', TIMESTAMP('2020-03-29-17.00.00'))
, ('Test6', TIMESTAMP('2020-03-31-19.00.00'))
) T (B, TS)
WHERE TS BETWEEN TIMESTAMP('2020-03-29-19.00.00') - 24 HOURS AND TIMESTAMP('2020-03-29-19.00.00');

The result is:

|B    |TS                        |
|-----|--------------------------|
|Test3|2020-03-29-05.00.00.000000|
|Test4|2020-03-29-13.00.00.000000|
|Test5|2020-03-29-17.00.00.000000|

Upvotes: 2

Related Questions