CODEWITHSUNDEEP
phpmysql
pravat231
pravat231

Reputation: 780

mysql select only next week data

I have a date field in my database i.e film_release_date.

So what is mysql query to fetch the next week data from table.

I run this query but its not working properlly.

SELECT 
    *,
    DATE_FORMAT(film_release_date,'%e-%b-%Y') AS release_date 
FROM gf_film 
WHERE 
    MONTH(film_release_date)=MONTH(CURRENT_DATE) 
    AND YEAR(film_release_date)=YEAR(CURRENT_DATE) 
    AND film_release_date>=(CURRENT_DATE) 
ORDER BY film_release_date DESC

Actually its for movie website i have several movies in my database so i need a query which fetch every next week movie that means movies that will release in next week means next friday.If i run this today i.e 13 then it shows all movies between 15 to 22 and if run this between 15 to 22 then it shows data from 22 to 29

Upvotes: 2

Views: 4894

Answers (3)

Marco
Marco

Reputation: 2892

Little late, but none of the answers works properly. You can use yearweek function to get week number of the year. So you get the week number of now (this week) or now() + interval 7 day to get next week. Examples:

THIS WEEK

SELECT *, DATE_FORMAT(film_release_date,'%e-%b-%Y') AS release_date 
  FROM gf_film 

WHERE YEARWEEK(film_release_date) = YEARWEEK(NOW())

ORDER BY film_release_date DESC

NEXT WEEK

SELECT *, DATE_FORMAT(film_release_date,'%e-%b-%Y') AS release_date 
  FROM gf_film 

WHERE YEARWEEK(film_release_date) = YEARWEEK(NOW() + INTERVAL 7 DAY)

ORDER BY film_release_date DESC

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

set @d := date_add(CURRENT_DATE, interval 7 day);
set @week_start := @d - interval (dayofweek(@d) + 1) day;
set @week_end := @d + interval (6 - dayofweek(@d)) day;
SELECT *,
       DATE_FORMAT(film_release_date,'%e-%b-%Y') AS release_date
FROM gf_film
WHERE film_release_date between @week_start and @week_end
ORDER BY film_release_date DESC

Upvotes: 4

xkeshav
xkeshav

Reputation: 54016

to get next week data you can simple apply below logic :

SELECT something FROM tbl_name
    -> WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAYS)

REFERENCE

Upvotes: 0

Related Questions