DATEADD(DAY, -30, GETDATE())

Question

DATEADD(DAY, -30, GETDATE())

hurricaneDBA

SSCertifiable

Points: 5027
More actions
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

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

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

DesNorton SSC-Insane Points: 24381 More actions · Answer 1

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.

Andrey Right there with Babe Points: 780 More actions · Answer 2

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))

Tom Uellner SSCrazy Points: 2995 More actions · Answer 3

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())
)

dbodell Right there with Babe Points: 742 More actions · Answer 4

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.

as_1234 SSCrazy Points: 2859 More actions · Answer 5

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.