How to fetch data between weeks in different year in Mysql

Question

In my table there is a column for week and date. How I can fetch data between 2015 week 52 to 2016 week 2. sorry for my bad English :(

@NSNoob If you `flag` question as duplicate then it must contain acceptable answer. — Sadikhasan, Dec 30 '15 at 05:41
@Sadikhasan the question is about fetching data between two date ranges(The starting date of 52nd week of 2015 and ending date as 2nd week of 2016). He has problems in his database design (using multiple columns for date and week) but they don't make his problem unique, the way I see it. — NSNoob, Dec 30 '15 at 05:42
@NSNoob But link which you given do not contain acceptable answer. how can we trust on that answer? — Sadikhasan, Dec 30 '15 at 05:46
@Sadikhasan Do you mean "Accepted" answer? JKMurphy's answer looks acceptable enough to me but then again I haven't worked with SQL since college. Maybe you should flag it as duplicate with a question which you deem contains "acceptable" answers since you obviously are way qualified than me in this regard :) Pretty sure there must be loads of questions asking to fetch data between a date range on SO. — NSNoob, Dec 30 '15 at 05:50
No they are not. You are unnecessarily maintaining a seperate column for week number when you could simply [get starting date of week number from your frontend PHP](http://stackoverflow.com/questions/4861384/php-get-start-and-end-date-of-a-week-by-weeknumber). (The linked answer ignores DST so it goes on and off)After getting the respective dates, you could simply use it to perform a simple data range fetching query. — NSNoob, Dec 30 '15 at 06:29

score 7 · Accepted Answer · answered Dec 30 '15 at 05:35

7

Try this query which may solve your problem.

SELECT *
FROM TABLE
WHERE (WEEK(date)>=52 AND YEAR(date)=2015) OR 
      (WEEK(date)<=2 AND YEAR(date)=2016);

answered Dec 30 '15 at 05:35

Sadikhasan

18,365
21
80
122

thank you for the quick response, I will try this and let you know if its solve my problem – Vivek ab Dec 30 '15 at 05:49
this solves my problem – Vivek ab Dec 30 '15 at 06:21
And resolved mine as well. Thanks for the answer :) – cristianoms Oct 07 '19 at 20:00

NSNoob · Answer 2 · 2016-01-05T12:39:49.063

This is an alternative solution in which you don't need to record week numbers in your database, Seeing as Sadikhassan has already provided the solution to OP's original request

There is no need to maintain a separate column for your week number. Just keep record of the date and lose the week number column. As you are using PHP as your frontend, you can use this method to get Date from your desired week number and year.

$range_start = new DateTime();
$range_start->setISODate($year,$week_no);//give 2015 and 52 here
echo $range_start->format('d-M-Y'); // 21 DEC 2015


$range_end = new DateTime();
$range_end->setISODate($year,$week_no);//give 2016 and 2 here
echo $range_end->format('d-M-Y'); //11 January 2016

//IDK if this is necessary because I don't work in PHP but I am gonna convert it to strings
$start_date_str = $range_start->format('d-M-Y');
$end_date_str = $range_end->format('d-M-Y');

Now that you have got your respective starting and ending dates, you can use them to simply query:

$result = mysql_query("SELECT * FROM YOURTABLE WHERE date >= '" . $start_date_str . "' AND date <= '" . $end_date_str . "' ORDER by id DESC");

How to fetch data between weeks in different year in Mysql

2 Answers2