delete data more then 7 days old

[Niklos]

Hi

I need some help with a script

I want to delete users from the users table where the customgroup contains 'P', but only those users then 7 days old, and the day where the users was entered are stored in a table call extrafield in the colum extra10 in this format
dd-mm-yyyy

i made this script

delete from users where customgroup='P' and userid in(Select userid from EXTRAFIELD where((@now-@date(@int(@substring(EXTRA10,6,4)), @int(@substring(EXTRA10,3,2)), @int(@left(EXTRA10,2)))>=7)));

but it gives me a 00318 error

Can anyone help me to make a script that works

Regards

Niklos

 

[GuptaSteve]

No doubt you had your reasons for using a Char column to store date values, and also to use such meaningless colum names.
Either convert the EXTRA10 column to Date/Time, so you can compare directly to SYSDATE or try:

Delete from users
where customgroup='P' and
userid in(Select userid from EXTRAFIELD
where @DATE( @SUBSTRING( EXTRA10 ,6,4), @SUBSTRING( EXTRA10 ,3,2),@SUBSTRING( EXTRA10 ,0,2) ) < SYSDATE - 7;