New to sql need assistance-CodePudding

Hello and thank you in advance. I need to delete all records in a table where the CreatedDate is greater than x. That’s easy enough but when there is a ModifiedDate then I have to change the logic. I have to check if ModifiedDate is not null, use this value if not use the CreatedDate value.

Not applying the if/else statement correctly.

Delete * From tableX
Where…

If ModifiedDate is not null and < Getdate() - 30
Else
CreateDate < Getdate() - 30

CodePudding user response：

Use this where clause:

where coalesce(ModifiedDate, CreateDate) < dateadd(day, -30, getdate())

CodePudding user response：

Use case statment

Delete * From tableX
CASE WEHN  ModifiedDate is not null and < Getdate() - 30 THEN -- your logic
Else -- CreateDate < Getdate() - 30 END