CODEWITHSUNDEEP
databaseoracle-databaseoracle11g
Sameervb
Sameervb

Reputation: 421

How to delete records in a table which are created between a particular time window?

I have this requirement of deleting records from a table which are created daily from 9AM to 11PM. I have figured out something like this -

delete * FROM mytable 
WHERE TO_NUMBER(TO_CHAR(entry_date,'HH24MI')) BETWEEN 0900 AND 2300;

However this deletes all records which are created in the time window, whereas I want it only for the current day. How can I use the sysdate here ? Or is there any other approach ?

Thanks a lot.

Upvotes: 2

Views: 74

Answers (1)

Ilia Maskov
Ilia Maskov

Reputation: 1898

You have dropped the date part, you should do something like this

DELETE * FROM mytable WHERE entry_date BETWEEN trunc(SYSDATE)+9/24 and trunc(SYSDATE)+23/24

I used 9/24 and 23/24 because of date arithmetic works with days, so the 9/24 is 9 hours.

Upvotes: 3

Related Questions