Limit SQL results to last 7 days

Question

I have a simple SQL query that looks like this...

SELECT 
    COUNT (* )
AS
    count
FROM
    wlead
WHERE
    f_id = '765'

This works great but I would now like to limit the query to just return results from the last 7 days. There is a field called date_created which is formatted like this...

2014-10-12 11:31:26

Is there an automatic way of limiting the results or do I need to work out the date 7 days previous first?

What database is this for? – OldProgrammer Nov 26 '14 at 19:42 — OldProgrammer, Nov 26 '14 at 19:42

score 1 · Answer 1 · edited May 23 '17 at 12:05

Yes - you can use the DATEADD() function and the GETDATE() function for this.

SELECT 
    COUNT(*) AS count
FROM
    wlead
WHERE
    f_id = '765' AND
    date_created >= DATEADD(DAY, -7, GETDATE())

Please note that this will return records created exactly seven days ago (so, if run now, records created after 2:45 PM EST). If you need to return everything from midnight on, you could use this to strip out the time portion and return midnight:

SELECT 
    COUNT(*) AS count
FROM
    wlead
WHERE
    f_id = '765' AND
    date_created >= DATEADD(dd, DATEDIFF(dd, 0, getdate()) -7, 0)

How do you know which database he uses ? – igr Nov 26 '14 at 20:01 — igr, Nov 26 '14 at 20:01

score 1 · Answer 2 · answered Nov 26 '14 at 19:43

1

Assuming mysql

..AND date_created<= NOW() AND date_created>= DATE_SUB(NOW(), INTERVAL 7 DAY)

answered Nov 26 '14 at 19:43

Mihai

26,325
7
66
81

score 1 · Answer 3 · answered Nov 26 '14 at 19:46

1

MySQL will like this:

... WHERE DATEDIFF(getdate(), date_created) <= 7

answered Nov 26 '14 at 19:46

kiks73

3,718
3
25
52

score 1 · Accepted Answer · answered Nov 26 '14 at 19:50

As you didn't mention your DBMS, the following is ANSI SQL:

select count(*) as cnt
from wlead
where f_id = '765'
  and date_created >= current_date - interval '7' day;

If date_created is actually a timestamp, you might want to compare that to a timestamp as well:

select count(*) as cnt
from wlead
where f_id = '765'
  and date_created >= current_timestamp - interval '7' day;

Note that this does not take care of "removing" the time part. current_timestamp - interval '7' day will yield a timestamp 7 days a ago at the same time (e.g. 20:52)

To "ignore" the time part you could cast date_created to a date:

select count(*) as cnt
from wlead
where f_id = '765'
  and cast(date_created as date) >= current_date - interval '7' day;

Limit SQL results to last 7 days

4 Answers4