CODEWITHSUNDEEP
sqldb2
Chris Quibell
Chris Quibell

Reputation: 339

Delete records that are older than a day

I have a table of links. If the link was generated and then the next day it is still in the table than I want to delete it. If there are multiple rows I want to remove each row.My query is: 

DELETE FROM resetpassword WHERE expiry_timestamp < DATEADD(day, -1, GETDATE())

but this gives me an error: 

com.web.command.exceptions.DatabaseException:
  "DAY" is not valid in the context where it is used.

How do I remove all rows that are a day old?

EDIT

expiry_timestamp is a timestamp so I think the query should be something like the below, but I still can't get it to work.

select * from resetpassword
where timestamp(expiry_timestamp) = timestamp(current date) - 1 days

Upvotes: 2

Views: 11344

Answers (3)

anupriya
anupriya

Reputation: 1

I think this could be an answer:

DELETE FROM resetpassword WHERE expiry_timestamp < DATEADD(dd, -1, GETDATE());

Upvotes: 0

WarrenT
WarrenT

Reputation: 4542

If expiry_timestamp is defined as a timestamp, you should 

DELETE FROM resetpassword WHERE expiry_timestamp < CURRENT TIMESTAMP - 1 day

It is generally better to avoid type conversion (such as timestamp to date) unless there is some need to do so.

Upvotes: 0

Dylan Brams
Dylan Brams

Reputation: 2099

Note: this is a DB2 specific answer.

Okay, this post here: http://www.dbforums.com/db2/1637371-help-there-dateadd-function-db2.html

(and this post agrees): http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html

says you can do:

   DELETE FROM resetpassword WHERE expiry_timestamp < (current date - 1 DAYS)

Upvotes: 3

Related Questions