Best Way to Find Records Created or Modified in the Past "X" Days?

Question

I'm looking to only select records from a table that have been added or modified within the past "X" number of days, let's say 5 days.

The Accounts table has two columns: CreateWhen and UpdateWhen.

I am using the following:

Select * from Accounts where CreateDate >= getdate() -5 or UpdateWhen >= getdate() -5

Is this the most efficient way to handle this request? It seems to work.

Answer 1

You didn't say what database engine you're using. Here's solution that should work with SQL Server:

SELECT *
FROM Accounts
WHERE DATEADD(d, 5, CreateDate) >= GETDATE()
OR DATEADD(d, 5, UpdateWhen) >= GETDATE()

It uses DATEADD function which allows you to add different time intervals to DateTime values. In this example you add 5 days (that's what d is for as first parameter).

You could do it the other way: add -5 days to GETDATE() and compare with UpdateWhen and CreateDate directly:

CreateDate >= DATEADD(d, -5, GETDATE())

Pick one that you think is more readable and easier to understand.