CODEWITHSUNDEEP
mysqlsqldatetime
MattCom
MattCom

Reputation: 259

How to query date in SQL for data in the last week

My current query is:

SELECT COUNT(*) FROM Previous_Appointment WHEREapDateBETWEEN '2017-04-03' AND '2017-04-27'

I need this to run however to just check today's date -7 days, -30 days, etc... So that it returns the number of instances that have occured.

Upvotes: 1

Views: 4042

Answers (3)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

To get count in last week:

select count(*)
from previous_appointment
where apDate between curdate() - interval 7 day and curdate()

To get count in last 30 days:

select count(*)
from previous_appointment
where apDate between curdate() - interval 30 day and curdate()

Upvotes: 1

Kushan
Kushan

Reputation: 10695

Try this,

Between today and today -7

SELECT COUNT(*) FROM Previous_Appointment WHERE DATE(apDate) > (NOW() - INTERVAL 7 DAY)

Between today and today -30

SELECT COUNT(*) FROM Previous_Appointment WHERE DATE(apDate) > (NOW() - INTERVAL 30 DAY)

Upvotes: 2

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

you can use dateadd -7 days

dateadd(day,-7,cast(getdate() as date))

Upvotes: 1

Related Questions