SQL Date Issues? Try Removing The Time

Image created by AI when I put in “SQL Date Range”

I needed to query my dataset for data within the past 4 weeks.

I used this code in my WHERE statement:

SELECT * FROM myTable
WHERE 
the_date >= DATEADD(WEEK, -4, DATEADD(dd, -(DATEPART(dw, GETDATE())-1), GETDATE()))

But it didn’t give the intended results.

The Problem: Time

Running the query above, because of the GETDATE() you’ll get a value with a timestamp:

SELECT DATEADD(WEEK, -4, DATEADD(dd, -(DATEPART(dw, GETDATE())-1), GETDATE()))

My field was set to date instead of datetime. This means that any values on that day- which translated to midnight- were excluded from the query.

The Solution

Format it such that there’s no timestamp. Put this in the WHERE criteria:

FORMAT ( DATEADD(WEEK, -4, DATEADD(dd, -(DATEPART(dw, GETDATE())-1), GETDATE())), 'yyyy-MM-dd')