Mitchel Stuart Fountaine Mitchel Stuart Fountaine - 7 months ago 10
SQL Question

Getdate() functionality returns partial day in select query

I have a query -

SELECT * FROM TABLE WHERE Date >= DATEADD (day, -7, -getdate()) AND Date <= getdate();


This would return all records for each day except day 7. If I ran this query on a Sunday at 17:00 it would only produce results going back to Monday 17:00. How could I include results from Monday 08:00.

Answer

Try it like this:

SELECT * 
FROM SomeWhere 
WHERE [Date] > DATEADD(HOUR,8,DATEADD(DAY, -7, CAST(CAST(GETDATE() AS DATE) AS DATETIME))) --7 days back, 8 o'clock
  AND [Date] <= GETDATE(); --now