CODEWITHSUNDEEP
mysql
Rob F.
Rob F.

Reputation: 11

Sorting from Today to Yesterday

I am trying to order a query by date, from today until yesterday. So far I can do this with two queries

SELECT title, premiered 
FROM movies 
WHERE DATE_FORMAT(premiered, '%m-%d') >= DATE_FORMAT(NOW(), '%m-%d') 
ORDER BY MONTH(premiered) ASC, DAY(premiered) ASC

Then

SELECT title, premiered 
FROM movies 
WHERE DATE_FORMAT(premiered, '%m-%d') < DATE_FORMAT(NOW(), '%m-%d') 
ORDER BY MONTH(premiered) ASC, DAY(premiered) ASC

I can not seem to combine these two queries and have the output sorted correctly.

Upvotes: 0

Views: 64

Answers (1)

forpas
forpas

Reputation: 164099

You want premiered to have a difference from CURRENT_DATE 0 or 1 days:

SELECT title, premiered 
FROM movies 
WHERE DATEDIFF(CURRENT_DATE, premiered) IN (0, 1)
ORDER BY premiered

Edit for the new requirement:

SELECT title, premiered 
FROM movies 
WHERE premiered BETWEEN CURRENT_DATE - INTERVAL 1 DAY AND CURRENT_DATE + INTERVAL 1 MONTH 
ORDER BY premiered

Upvotes: 1

Related Questions