IBM Mainframe Forum

by **jishamary** » Mon Aug 10, 2009 12:47 pm

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

by **nayanpatra** » Tue Aug 11, 2009 12:03 pm

in your select query include a where clause Date_field > '090511'

by **jishamary** » Tue Aug 11, 2009 1:16 pm

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

by **GuyC** » Tue Aug 11, 2009 4:33 pm

where column < substr(char(year(current_date - 3 months) * 10000 + month(current_date- 3 months) * 100 + day(current_date - 3 months ) ),3,6)

by **jishamary** » Wed Aug 12, 2009 11:56 am

Hi GuyC,
Thank you very much. It worked !!!!

Thanks,
Jisha

by **venki3** » Tue Feb 16, 2010 5:34 pm

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

by **GuyC** » Mon Feb 22, 2010 1:32 pm

year() = 2010
month() = 02
day() = 22

2010 * 10000 + 02 * 100 + 22 = 20100222

substr('20100222',3,6) = 100222

IBM Mainframe Forum

How to delete old data from table whose date field is X(6)

How to delete old data from table whose date field is X(6)

Re: How to delete old data from table whose date field is X(6)

Re: How to delete old data from table whose date field is X(6)

Re: How to delete old data from table whose date field is X(6)

Re: How to delete old data from table whose date field is X(6)

Re: How to delete old data from table whose date field is X(6)

Re: How to delete old data from table whose date field is X(6)