Auto delete rows older than a month

[Steve]

delete from MYTABLE where date(MYCOLUMN) = current date - 30

"MYCOLUMN" is of type timestamp.

This query doesnt' work. Anything I missed here? I am hoping to
automate it on a daily basis. Anyone knows a better way to do it? Much
appreciated.

[Knut Stolze]

Steve wrote:

delete from MYTABLE where date(MYCOLUMN) = current date - 30

"MYCOLUMN" is of type timestamp.

This query doesnt' work. Anything I missed here? I am hoping to
automate it on a daily basis. Anyone knows a better way to do it? Much
appreciated.

You probably meant:

delete from MYTABLE where date(MYCOLUMN) = current date - 30 DAYS

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Gert van der Kooij]

In article <11**********************@g44g2000cwa.googlegroups .com>,
Steve (ji*********@gmail.com) says...

delete from MYTABLE where date(MYCOLUMN) = current date - 30

"MYCOLUMN" is of type timestamp.

This query doesnt' work. Anything I missed here? I am hoping to
automate it on a daily basis. Anyone knows a better way to do it? Much
appreciated.

delete from MYTABLE where date(MYCOLUMN) = current date - 30 days

[bughunter]

where date(MYCOLUMN) = current date - 30 DAYS

But this query will be not optimal, because it will be indexscan by
mycolumn index.

If query is long rewrite it like

where MYCOLUMN >= timestamp(current date - 30 DAYS,'00:00') and
MYCOLUMN < timestamp(current date - 29 DAYS,'00:00')

Andy

Steve wrote:

delete from MYTABLE where date(MYCOLUMN) = current date - 30

"MYCOLUMN" is of type timestamp.

This query doesnt' work. Anything I missed here? I am hoping to
automate it on a daily basis. Anyone knows a better way to do it? Much
appreciated.

[Steve]

"current date - 30 days" does what I need. Thanks a lot!