CODEWITHSUNDEEP
phpmysqldate
Squadrons
Squadrons

Reputation: 2557

Select dates between this monday and this friday

I have a table with a date field (2013-07-11 = July 11th, 2013).

I need to select entries from the monday (the start of my week) of any given week until the end of that week.

I've seen queries that can grab rows whose date is 1 week ago, but I specifically need to grab rows that have a date field that occurs this week, regardless of what the current day of the week is.

I'm currently grabbing it in php using this, but I feel like there has to be a mysql method for calculating the beginning and end of this week:

$this_monday = date('Y-m-d', strtotime('previous monday'));
$this_friday = date('Y-m-d',strtotime('this friday'));

$sql = "SELECT `date`, $sign FROM `horoscopes` 
    WHERE `date` >= '$this_monday' AND `date` <= '$this_friday'
    AND type = 'Daily'
    ORDER BY `date` ASC
";

And I'm pretty sure that on monday it will end up grabbing monday of last week, not monday.

from this data set I'd like to select the following:

+------------+
| date       |
+------------+
| 2013-07-12 |
| 2013-07-11 |
| 2013-07-10 |
| 2013-07-09 |
| 2013-07-17 |
| 2013-07-08 |
| 2013-07-05 |
+------------+

7-8, 7-9, 7-10, 7-11, 7-12 regardless of the day of the week.

Upvotes: 1

Views: 920

Answers (1)

Squadrons
Squadrons

Reputation: 2557

Just learned about the WEEK() function in mysql 

SELECT `date` FROM horoscopes WHERE WEEK(`date`) = WEEK(NOW(), -1);

Upvotes: 5

Related Questions