CODEWITHSUNDEEP
mysqlsql
Thomas Jones
Thomas Jones

Reputation: 11

SQL - Retrieve Records from Day Before Yesterday

I"m trying to retrieve records from the day before yesterday. The query below returns the records from yesterday...

SELECT * FROM table WHERE `DATE` > DATE_ADD(CURDATE(), INTERVAL -2 DAY) AND `DATE` < CURDATE()

The following query returns the records from the day before AND yesterday...

SELECT * FROM table WHERE `DATE` > DATE_ADD(CURDATE(), INTERVAL -3 DAY) AND `DATE` < CURDATE()

Though I'd really like to obtain records for ONLY the day before yesterday. I'm trying the following query, but it's not working...

SELECT * FROM table WHERE `DATE` > DATE_ADD(CURDATE(), INTERVAL -3 DAY) AND `DATE` < DATE_ADD(CURDATE(), INTERVAL -2 DAY)

Could someone point me in the right direction?

Upvotes: 1

Views: 7013

Answers (3)

Zach Leighton
Zach Leighton

Reputation: 1941

This may work, or a combination of >= and <

Basically, you're looking for a date range that can't exist.

SELECT * FROM table WHERE `DATE` = DATE_ADD(CURDATE(), INTERVAL -2 DAY)

Upvotes: 0

chetan
chetan

Reputation: 2896

try this:- 

SELECT * FROM table WHERE DATE = CURDATE() - 2;

or

SELECT * FROM table where DATE = DATE_SUB(CURDATE(),INTERVAL 2 DAY);

Upvotes: 1

Erik Lukiman
Erik Lukiman

Reputation: 337

You can just use a simpler method, without DATE_ADD or DATE_SUB : 

SELECT * FROM table WHERE DATE = CURDATE() - INTERVAL 2 DAY

to retrive records from yesterday : 

SELECT * FROM table WHERE DATE = CURDATE() - INTERVAL 1 DAY

But if your DATE column is a DATETIME : 

SELECT * FROM table WHERE DATE BETWEEN CURDATE() - INTERVAL 2 DAY AND CURDATE() - INTERVAL 1 DAY

Upvotes: 1

Related Questions