get data from last week

Question

I want all data from last week. I used

SELECT id FROM tbl
WHERE date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND date < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY

but its not working when my week starts with "Monday".

What should I do?

The problem is you're using current date, not "monday" or whatever as you start point. so on thursday it would run between 6 days ago and yesterday.. — BugFinder
– BugFinder, Commented Sep 19, 2012 at 11:44
I want that if I start from whatever date it should always display last week starting from monday to sunday. — Moumita
– Moumita, Commented Sep 19, 2012 at 11:47
Exacrtly you havent taken into account that today is not Monday. — BugFinder
– BugFinder, Commented Sep 19, 2012 at 11:48
@BugFinder This sql return data with starting from sunday to monday. but I want monday to sunday. if I use +5 it work for only if current date is sunday, but when I change date its not work. — Moumita
– Moumita, Commented Sep 19, 2012 at 11:54

Moumita · Accepted Answer · 2012-09-19 13:40:03Z

1

SELECT id FROM tbl WHERE date  >= CURDATE() - INTERVAL (WEEKDAY(CURDATE())+7) DAY  AND date   <  CURDATE()  - INTERVAL (WEEKDAY(CURDATE())) DAY

I try this and it work for me.

answered Sep 19, 2012 at 13:40

Moumita

3609 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Salil · Accepted Answer · 2012-09-19 11:46:14Z

0

Try

SELECT id FROM tbl
WHERE YEARweek(date) = YEARweek(curdate())

answered Sep 19, 2012 at 11:46

Salil

47.7k22 gold badges127 silver badges161 bronze badges

Comments

GautamD31 · Accepted Answer · 2012-09-19 11:52:37Z

0

Then change like this

$lastweek = unix_to_human(time("Y-m-d H:i:s") - (7 * 24 * 60 * 60), TRUE, 'us');
SELECT id FROM tbl
WHERE date >= curdate() - $lastweek
AND date < curdate() - $lastweek

edited Sep 19, 2012 at 11:52

answered Sep 19, 2012 at 11:45

GautamD31

28.8k10 gold badges66 silver badges86 bronze badges

2 Comments

Moumita Over a year ago

if I use +5 it work for only if current date is sunday, but when I change date its not work.

Moumita Over a year ago

my project in cakephp here unix_to_human returns an error : Fatal error: Call to undefined method AdminStatisticController::unix_to_human()

Collectives™ on Stack Overflow

get data from last week

3 Answers 3

Comments

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Linked

Related