CODEWITHSUNDEEP
phpmysqlsqltime
user3353408
user3353408

Reputation:

Is date in database between today and last week

I have been trying to find out how to write a SQL query to get the number of users that have registered in the system during the last week. This is my query right now:

SELECT count(id) FROM profile WHERE registration_date BETWEEN $week_ago AND $current_date

It renders an error. The php variables used are defined here:

$week_ago = date('Y m d H:i:s', strtotime('-1 week'));
$current_date = date('Y m d H:i:s', time());

What am I doing wrong?

Upvotes: 2

Views: 72

Answers (4)

Programmer
Programmer

Reputation: 60

Use quotes around $week_ago and $current_date in you SQL statement..

SELECT count(id) FROM profile WHERE registration_date BETWEEN '$week_ago' AND '$current_date'

Upvotes: 0

Navneet
Navneet

Reputation: 447

in SQL it was as below 

SELECT count(id) 
FROM profile 
WHERE registration_date BETWEEN (Cast(GetDate() as date)-7)
                            AND Cast(GetDate() as date)

Or u try this one also

SELECT count(id) 
FROM profile 
WHERE registration_date >= (Cast(GetDate() as date)-7)
                            AND registration_date <= Cast(GetDate() as date)

Upvotes: 1

Ende Neu
Ende Neu

Reputation: 15783

I'm not sure about your php, but you can do this in sql:

SELECT count(id) 
FROM profile 
WHERE registration_date 
BETWEEN NOW() - INTERVAL 7 DAY 
  AND NOW()

Upvotes: 1

juergen d
juergen d

Reputation: 204854

You can do it in pure SQL 

SELECT count(id) 
FROM profile 
WHERE registration_date BETWEEN now() -  interval 7 day 
                            AND now()

Upvotes: 2

Related Questions