CODEWITHSUNDEEP
sqlsql-server
user3353723
user3353723

Reputation: 219

How to add a Date Range in the WHERE clause?

I have a query in SQL Server that I want to run every week which would display the data from the last 7 days.

I tried using:

SELECT something FROM tbl_name
WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= date_col;

However, the above code is for MYSQL but I have SQL Server installed and I was wondering if anyone had a solution for this problem.

Upvotes: 1

Views: 4068

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Try this:

SELECT something
FROM tbl_name
WHERE date_col >= dateadd(day, -7, getdate());

If you want to be sure the time component is removed (as suggested by CURDATE()):

SELECT something
FROM tbl_name
WHERE date_col >= cast(dateadd(day, -7, getdate()) as date);

EDIT:

The answer to the question in your comment is:

SELECT something
FROM tbl_name
WHERE date_col >= cast(dateadd(day, -7, getdate()) as date) and
      date_col < cast(dateadd(day, 1, getdate()) as date);

Note the second condition is <, not <=. If date_col has no time component, then:

WHERE date_col >= cast(dateadd(day, -7, getdate()) as date) and
      date_col <= cast(getdate() as date)

also works.

Upvotes: 2

Related Questions