0

We are using the below code in the WHERE clause of an sql query to return a date of 7 days ago. However, I don't actually understand how it is working.

dateadd(day,datediff(day,7,GETDATE()),0)

I've read about the DATEDIFF() and DATEADD() functions but they don't match the above.

For example:

DATEDIFF() should be (interval, date, date), but above it is (interval, number, date).

DATEADD() should be (interval, number, date), but above it just has a 0 for the date.

The thing is, it works. It gives the expected output of 7 days ago.

Can anyone help me understand why and how this is working?

Thanks

2

That's pretty contorted, for sure!

Dates are stored internally as numbers (the number of days since midnight on 1st Jan 1900). So, casting 7 as a Date would be result in a date of 1900-01-08 00:00:00. As of 23rd August 2020, the difference in days between that date and the current date is 44,057 days. If you cast 44,057 as a Date (using the dateadd() function), you get the date of seven days ago - 16th August 2020.

There are quite a number of ways to achieve the above, but possibly a simpler way of constructing the clause would be the following:

SELECT *
FROM MyTable
WHERE DateDiff(day,MyDateField,GetDate()) = 6 /* six whole days between then and now */
2
  • Thanks Macca. Would the simple way of doing it just be DATEADD(day,-7,GETDATE()) ? – Ben Aug 24 '20 at 23:09
  • I've added one of the myriad of possible solutions to this, but you'd need to CAST/CONVERT your DateTimes to Dates in your example to round the days down to midnight of the day in question. – Macca Aug 25 '20 at 1:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.