CODEWITHSUNDEEP
mysql
ViPZoMbie1
ViPZoMbie1

Reputation: 13

Selecting data from database which is from this week except today

I need some help with some a simple query. I want to select data from a table where the date is in this week, but I don't want the records where the date is today. So if today is Monday, then I only want the data from Tuesday to Friday.

This was my best attempt.

SELECT * 
FROM data 
WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK)

How can I improve this?

Upvotes: 0

Views: 58

Answers (3)

Attila Naghi
Attila Naghi

Reputation: 2686

You can use between and subdate

SELECT * FROM `data` WHERE `date`
BETWEEN SUBDATE(CURDATE(), 7) AND SUBDATE(CURDATE(), 1);

Upvotes: 1

Eydamos
Eydamos

Reputation: 601

You can use between here and go one day back from today.

SELECT * FROM data WHERE date BETWEEN DATE_SUB(NOW(), INTERVAL 1 WEEK) AND DATE_SUB(CURDATE(), INTERVAL 1 DAY)

Be aware that I have used CURDATE() instead of NOW() as curdate does only contain the date without time.

Upvotes: 0

Kasia Gogolek
Kasia Gogolek

Reputation: 3414

You want to use CURDATE()

The CURDATE() function returns the current date.

Note: This function returns the current date as a "YYYY-MM-DD" format if used in a string context, and as a YYYYMMDD format if used in a numeric context.

And DATE()

The DATE() function extracts the date value from a date or datetime expression.

SELECT * FROM data WHERE date > DATE_SUB(NOW(), INTERVAL 1 WEEK) AND DATE(date) < CURDATE();

This will effectively turn your data into YYYY-MM-DD and compare with today's YYYY-MM-DD

Upvotes: 0

Related Questions