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



IBM's flagship relational database management system

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

Postby 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
jishamary
 
Posts: 7
Joined: Mon May 11, 2009 3:12 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby nayanpatra » Tue Aug 11, 2009 12:03 pm

in your select query include a where clause Date_field > '090511'
nayanpatra
 
Posts: 19
Joined: Mon Jul 06, 2009 4:35 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby 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
jishamary
 
Posts: 7
Joined: Mon May 11, 2009 3:12 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby 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)
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

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

Postby jishamary » Wed Aug 12, 2009 11:56 am

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

Thanks,
Jisha
jishamary
 
Posts: 7
Joined: Mon May 11, 2009 3:12 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby 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
venki3
 
Posts: 4
Joined: Tue Dec 22, 2009 2:10 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby 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
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post