CODEWITHSUNDEEP
sqloracle-database
Kumar Prasad
Kumar Prasad

Reputation: 1

how to fetch the records from the table for last 24 hrs

I want to find records in a (Oracle SQL) table using the creation date field where records are older than 24hrs and set some status once. To find records using a operators like > but if anyone can suggest quick SQL where clause statement to find records older than 24hrs that would be nice.

Upvotes: 0

Views: 202

Answers (2)

Caius Jard
Caius Jard

Reputation: 74710

Get records younger than 24 hours:

SELECT * FROM table 
WHERE record_date > SYSDATE - 1

Get records older than 24 hours:

SELECT * FROM table 
WHERE record_date < SYSDATE - 1

Get records from today:

SELECT * FROM table 
WHERE record_date >= TRUNC(SYSDATE)

Get records from before today:

SELECT * FROM table 
WHERE record_date < TRUNC(SYSDATE

Set records older than 24 h to old status:

UPDATE table SET status = 'old'
WHERE record_date < SYSYDATE - 1

Remember that adding/subtracting numbers to dates in oracle adds or removes that number of days or part days. 

01-Jan-1970 00:00:00 + 1.5 = 02-Jan-1970 00:06:00

Let's add 3 days 17 hours and 26 minutes:

01-Jan-1970 00:00:00 + 3 + (17/24) + (26/60/24)

Upvotes: 0

Rohit RC
Rohit RC

Reputation: 469

You can use below query to fetch the same

SELECT * FROM mytable WHERE record_date >= SYSDATE - 1

Upvotes: 1

Related Questions