Post reply

DATEADD(DAY, -30, GETDATE())

  • July 24, 2019 at 9:32 am

    #3665613

    Dear Everyone

    I wanted to know the statement below will it delete everything in the last 30 days or anything before the last 30 days?

    DELETE FROM tb_LogIP WHERE MsgLogID IN (SELECT MsgLogID FROM tb_LogGeneral WITH (NOLOCK) WHERE CLF_LogReceivedTime < DATEADD(DAY, -30, GETDATE()))

    I think this query will delete the most recent data in the last 30 days not older than 30 days

    please confirm

    Kal

     

  • July 24, 2019 at 9:35 am

    #3665614

    hurricaneDBA wrote:

    Dear Everyone I wanted to know the statement below will it delete everything in the last 30 days or anything before the last 30 days? DELETE FROM tb_LogIP WHERE MsgLogID IN (SELECT MsgLogID FROM tb_LogGeneral WITH (NOLOCK) WHERE CLF_LogReceivedTime < DATEADD(DAY, -30, GETDATE())) I think this query will delete the most recent data in the last 30 days not older than 30 days please confirm Kal  

    This statement will delete all records older than 30 days.

    How to post data/code on a forum to get the best help.

  • July 24, 2019 at 10:09 am

    #3665630

    getdate() returns time also,  so in the current case not all records created 30 days ago will be deleted

    to delete records by days , it's better to use cast to date type

    example:

    dateadd(dd,-30,cast (getdate() as date))

  • August 1, 2019 at 5:35 pm

    #3667995

    Also, a simple way to test this would be to just replace the DELETE with SELECT * and see what comes back.

    SELECT * FROM tb_LogIP 
    WHERE MsgLogID IN (
        SELECT MsgLogID FROM tb_LogGeneral WITH (NOLOCK) 
        WHERE CLF_LogReceivedTime < DATEADD(DAY, -30, GETDATE())
    )

  • August 6, 2019 at 2:45 pm

    #3669178

    I agree that this would delete all records where the CLF_LogReceivedTime is EARLIER than 30 days ago. It would have to be WHERE CLF_LogReceivedTime > DATEADD(DAY,-30,GETDATE()) to delete the records from the most recent 30 days.

  • August 7, 2019 at 10:39 am

    #3669411

    Tom Uellner wrote:

    Also, a simple way to test this would be to just replace the DELETE with SELECT * and see what comes back.

    I am by no means a SQL expert but this is a process that I use regularly. It's a really good way to check that the delete will remove the records you intend especially as in most, if not all cases, you only need to change the select keyword to delete if the results are as you expect. It's saved me from deleting the wrong records quite a few times.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply