CODEWITHSUNDEEP
sqloracle-database
John Ayers
John Ayers

Reputation: 519

SQL date check within set number of days

Im trying to find out how many clients viewed a property within 14 days of May 20, 2004, either before or after. Not really sure at all how to go about this.

Im assuming i need to group it and use a having?

EDIT: I am using oracle now

select count(*)
from VIEWING
WHERE CLAUSE?

Upvotes: 1

Views: 843

Answers (2)

Taryn
Taryn

Reputation: 247680

Edit #1, since you are using Oracle, you can use:

select count(*) TotalClients
from yourtable
where dt >= (to_date('2004-05-20', 'yyyy-mm-dd') - INTERVAL '14' DAY)
  and dt <= (to_date('2004-05-20', 'yyyy-mm-dd') + INTERVAL '14' DAY)

See SQL Fiddle with Demo

Based on some of your previous questions you were using MySQL.

If you are using MySQL then you can use the DATE_ADD() function to get the date range and then use count(*) to return all records from those dates:

select count(*) TotalClients
from yourtable
where dt >= date_add(str_to_date('2004-05-20', '%Y-%m-%d'), INTERVAL -14 day)
  and dt <= date_add(str_to_date('2004-05-20', '%Y-%m-%d'), INTERVAL 14 day)

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

For a one time query with that specific date,

select count(*) clients
from yourtable
where yourdatefield >= {d'2004-05-06'}
and yourdatefield < {d'2004-06-08'}

You might want to consult a calendar to see if those dates are correct.

Upvotes: 2

Related Questions