INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

delete data more then 7 days old

delete data more then 7 days old

(OP)

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


 

RE: delete data more then 7 days old


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;

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close