Page 1 of 1
How to delete old data from table whose date field is X(6)
Posted:
Mon Aug 10, 2009 12:47 pmby jishamary
Hi,
I have a requirement to delete 3 months old data from a table. But the problem is the Date field in this table is not defined as Date but as CHAR(6) in YYMMDD. Could you please suggest how to do this without writing a Cobol program.
Thanks In Advance,
Jisha
Re: How to delete old data from table whose date field is X(6)
Posted:
Tue Aug 11, 2009 12:03 pmby nayanpatra
in your select query include a where clause Date_field > '090511'
Re: How to delete old data from table whose date field is X(6)
Posted:
Tue Aug 11, 2009 1:16 pmby jishamary
Hi,
I need to delete 3 months older data from a table on a daily basis. So it can't be hardcoded but we should take the current date and do. In that case is there any way to do it?
Please suggest.
Thanks,
Jisha
Re: How to delete old data from table whose date field is X(6)
Posted:
Tue Aug 11, 2009 4:33 pmby GuyC
where column < substr(char(year(current_date - 3 months) * 10000 + month(current_date- 3 months) * 100 + day(current_date - 3 months ) ),3,6)
Re: How to delete old data from table whose date field is X(6)
Posted:
Wed Aug 12, 2009 11:56 amby jishamary
Hi GuyC,
Thank you very much. It worked !!!!
Thanks,
Jisha
Re: How to delete old data from table whose date field is X(6)
Posted:
Tue Feb 16, 2010 5:34 pmby venki3
Hi GuyC,
I could not understand the funda behind the multiplication of resultant year with 10000 and resultant month with 100.
could you please explain the same.
Thanks,
Venkateswari
Re: How to delete old data from table whose date field is X(6)
Posted:
Mon Feb 22, 2010 1:32 pmby GuyC
year() = 2010
month() = 02
day() = 22
2010 * 10000 + 02 * 100 + 22 = 20100222
substr('20100222',3,6) = 100222