Results 1 to 2 of 2

Thread: delete records older than 5 days .

  1. #1
    admin001 Guest

    delete records older than 5 days .

    Hello ,

    I am little confused in writing the exact query i.e filling the correct details in the query .
    To simplify let me explain....

    I am using the query as

    delete MYTABLE
    where datediff(dd,loaddate,getdate())>5

    I have a table now with loadate column which gets the default date and time . The loadate shows correct date and time when the data was imported in the table .

    Now suppose if i want to delete previous 5 days records from today
    ( for e.g today is 20/08/2002 3:40:00 PM ) ideally it should delete all records which are 5 days older from today . i.e from 20/08/2002 3:40:00 PM to 15/08/2002 3:40:00 PM )
    But when i execute the datediff command , it deletes the records previous than 15/08/2002 till 15/08/2002.
    The records from 15/08/2002 to 20/08/2002 remain intact .

    I am getting some different results .

    Am i missing something in the query or i am confused about the calculation of the dates the datediff command performs .

    Is the logic correct or i am missing someting important ?

    Thanks and Regards
    Admin001



  2. #2
    Breda Guest

    delete records older than 5 days . (reply)

    You just need to change your sign to less than instead of greater than.
    Currenly you are deleting records that are more than 5 days old but you really
    was the last 5 days so that is records that are less than 5 days old.


    ------------
    admin001 at 8/20/2002 9:53:00 AM

    Hello ,

    I am little confused in writing the exact query i.e filling the correct details in the query .
    To simplify let me explain....

    I am using the query as

    delete MYTABLE
    where datediff(dd,loaddate,getdate())>5

    I have a table now with loadate column which gets the default date and time . The loadate shows correct date and time when the data was imported in the table .

    Now suppose if i want to delete previous 5 days records from today
    ( for e.g today is 20/08/2002 3:40:00 PM ) ideally it should delete all records which are 5 days older from today . i.e from 20/08/2002 3:40:00 PM to 15/08/2002 3:40:00 PM )
    But when i execute the datediff command , it deletes the records previous than 15/08/2002 till 15/08/2002.
    The records from 15/08/2002 to 20/08/2002 remain intact .

    I am getting some different results .

    Am i missing something in the query or i am confused about the calculation of the dates the datediff command performs .

    Is the logic correct or i am missing someting important ?

    Thanks and Regards
    Admin001



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •