CODEWITHSUNDEEP
sql
fightstarr20
fightstarr20

Reputation: 12628

Limit SQL results to last 7 days

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?

Upvotes: 0

Views: 4049

Answers (4)

user330315
user330315

Reputation:

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;

Upvotes: 1

AHiggins
AHiggins

Reputation: 7227

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)

Upvotes: 1

kiks73
kiks73

Reputation: 3778

MySQL will like this:

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

Upvotes: 1

Mihai
Mihai

Reputation: 26804

Assuming mysql

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

Upvotes: 1

Related Questions