How do I SELECT the first week of a previous month

Question

How do I SELECT the first week of a previous month I've tried

$myQuery = "SELECT repairId , startDate,catId,statusId FROM repair 
  WHERE supermarketId = '$supermarket' 
  AND startDate>=(CURDATE()- 1 WEEK - INTERVAL 2 week)";

This was used to try and select the third week but this didn't work

Welcome to the site! Please take some time to read [this site's help section](http://stackoverflow.com/help), especially the pages ["What topics can I ask about here?"](http://stackoverflow.com/help/on-topic) and ["What types of questions should I avoid asking?"](http://stackoverflow.com/help/dont-ask). Also please read about [how to ask a good question](http://stackoverflow.com/help/how-to-ask) and how to create a [Minimal, Complete, and Verifiable Example](http://stackoverflow.com/help/mcve). — elixenide, Nov 20 '15 at 19:21
You can use SELECT BETWEEN http://stackoverflow.com/questions/5125076/sql-query-to-select-dates-between-two-dates — Gamenotcore, Nov 20 '15 at 19:35
This won't work because the query could be ask from any month, so what would I need to add to replace the specified dates? — RM1664, Nov 20 '15 at 19:55

score 0 · Accepted Answer · answered Nov 20 '15 at 20:42

Does this work for you:

$myQuery = "SELECT repairId , startDate,catId,statusId FROM repair 
  WHERE supermarketId = '$supermarket' 
  AND startDate>= curdate() - interval 1 month
   - interval weekday(curdate() - interval 1 month) day 
   - interval (day(curdate() - interval 1 month 
   - interval weekday(curdate() - interval 1 month) day) div 7) week 
  AND startDate < curdate() - interval 1 month
   - interval weekday(curdate() - interval 1 month) day 
   - interval (day(curdate() - interval 1 month 
   - interval weekday(curdate() - interval 1 month) day) div 7) week _+ interval 1 week";

?

The idea here is that we first go back a month, then find the start of the week (assuming Monday, for Sunday we will need some extra tweaking), then figure out how many whole weeks it has been from the start of the month, and subtract that many weeks from the date so far. This takes us back to the start of the first week of the month. For the end of the range we just add one week to the start.

startDate>=(CURDATE()-INTERVAL 4 week) AND startDate<=(CURDATE()-INTERVAL 3 week) "; — RM1664, Nov 20 '15 at 21:26

score 0 · Answer 2 · edited May 23 '17 at 12:30

Ah, your question became more detailed.

Not really familiar with sql, there might be better but something like:

SELECT repairId , startDate,catId, statusId FROM repair
WHERE EXTRACT(YEAR_MONTH FROM start_date) = EXTRACT(YEAR_MONTH FROM NOW()) - 1 AND CAST(EXTRACT(DAY FROM start_date) / 7 + 1 as INT) = ?;

Basically, extract the year month components to compare year and month and then extract the day of month use the flooring caused by integer truncation to get the week to compare with whatever week you are looking for

mysql return rows matching year month

How do I SELECT the first week of a previous month

2 Answers2