Return rows between two dates in MySQL

Question

I would like to rows that have only been entered in the last 1 day.

I have a date column which stores YYYY-MM-DD, and I allow the user to send a date that they want to look at in this format yyymmdd how can I use this data to limit it to the previous day only?

I would imagine it is something to do with the BETWEEN keyword but I cant figure it out.

previous day = yesterday or (yyyymmdd - one day)?

jgauffin
– jgauffin

2011-03-22 15:42:37 +00:00
Commented Mar 22, 2011 at 15:42 — jgauffin
– jgauffin, Commented Mar 22, 2011 at 15:42

K M Rakibul Islam · Accepted Answer · 2014-12-23 20:47:27Z

10

SELECT * from TABLE_NAME WHERE ROW_DATE BETWEEN '2011-03-20' AND '2011-03-21'

edited Dec 23, 2014 at 20:47

K M Rakibul Islam

34.4k13 gold badges95 silver badges113 bronze badges

answered Mar 22, 2011 at 15:42

David Machel

2962 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Quassnoi Over a year ago

This will return records for 2011-03-21 00:00:00 as well.

Quassnoi · Accepted Answer · 2011-03-22 15:42:23Z

7

This query:

SELECT  *
FROM    mytable
WHERE   mydate >= STR_TO_DATE('110321', '%y%m%d') - INTERVAL 1 DAY
        AND mydate < STR_TO_DATE('110321', '%y%m%d')

will return all records for Mar 20, 2011

answered Mar 22, 2011 at 15:42

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

3 Comments

nhunston Over a year ago

this works for me if I used the string '110321' however it doesn't if I use "STR_TO_DATE('$testDate', '%y%m%d')" it doesn't $testDate = $_POST['date']; $testDate = (string)$testDate); The format of $testDate = YYYYMMDD

Quassnoi Over a year ago

@Herly: use %Y%m%d (note the uppercase Y) for four-digit years.

nhunston Over a year ago

Nevermind it WAS because i was using YYYMMDD instead of YYMMDD. thanks for your help.

Bojangles · Accepted Answer · 2011-03-22 15:43:38Z

1

From the MySQL manual (here):

SELECT something FROM tbl_name WHERE DATE_SUB(CURDATE(), INTERVAL 1 DAY) <= date_col;

answered Mar 22, 2011 at 15:43

Bojangles

102k50 gold badges175 silver badges209 bronze badges

Comments

Mike Lewis · Accepted Answer · 2011-03-22 15:44:11Z

1

SELECT * FROM YourTable WHERE date_column = DATE_ADD(CURDATE(), INTERVAL -1 DAY)

This returns all rows for today and yesterday.

answered Mar 22, 2011 at 15:44

Mike Lewis

64.3k20 gold badges144 silver badges111 bronze badges

Collectives™ on Stack Overflow

Return rows between two dates in MySQL

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related