Delete records that are older than a day

Question

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 

Solution

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)

Comments

[Dylan Brams]

Is the datatype on expiry_timestamp a datetime? Because this query should work as-is. It parses and executes in my t-SQL window.

[Chris Quibell]

I get this error when I use that query: No authorized routine named "GETDATE" of type "FUNCTION" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.16.53

[Dylan Brams]

Yeah, I noticed that. Try the edited version I just put together. I've been monkeying with the syntax a bit, reload.

[Dylan Brams]

And while I'm recommending things I suggest using a SELECT * instead of a delete while testing. Copy-pasting random stuff people tell you to do on the Interwebs is a bad habit to get into.

[Chris Quibell]

I inserted dumby data into the database then I deleted it,that way I knew what I was removing and I didn't wipe out a whole table.