mySQL dateTime range Query Issue

Question

I have a quick question. I'm have a db an audit Table with a datetime column in it. (i.e 2012-03-27 00:00:00) and I'm building a mySQL query to return a set of rows if the date is in between the two dates I'm giving it.

so far my query looks like this:

SELECT * FROM util_audit WHERE DATED >= DATE(03/15/2012) AND DATED <= DATE(03/31/2012);

if I just use

SELECT * FROM util_audit WHERE DATED >= DATE(03/15/2012);

It'll return all my record because they were dated this week.

I also tried this:

SELECT * FROM util_audit WHERE DATED >= '02/15/2012 00:00:00' AND DATED <= '03/31/2012 00:00:00';

and nothing! It'll return zero rows, when I know I have all of them dated from the 27 of this month to today. Am I missing something here? why does it work on its own, but not when I add the second date?I'm probably overlooking something.

Adi Gandra · Accepted Answer · 2012-03-30 01:02:14Z

56

Try:

SELECT * FROM util_audit WHERE `DATED` BETWEEN "2012-03-15" AND "2012-03-31";

answered Mar 30, 2012 at 1:02

Adi Gandra

6616 silver badges5 bronze badges

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

4 Comments

khany Over a year ago

What happens when you have more search criteria (eg. SELECT * FROM util_audit WHERE DATED BETWEEN "2012-03-15" AND "2012-03-31" AND booLive = 1)? Because I get errors.

Jamie Ciocco Over a year ago

I can't reproduce that error, but in any case you can always use parentheses for clarity, e.g. SELECT * FROM util_audit WHERE (DATED BETWEEN "2012-03-15" AND "2012-03-31") AND booLive = 1;

Jamie Ciocco Over a year ago

To get ALL the data for a specific month/year, you can always start the day at 00 and go to 31 (no matter what the month). This will also capture dates where the day portion is unknown, such as 2015-02-00. So for example: SELECT * FROM records WHERE start_date BETWEEN "2015-02-00" AND "2015-02-31";

Michael K Over a year ago

You can no longer use the 31st of the month for months that don't have 31 days in newer versions of MySQL. This must have changed sometime between v5 and v8. It now gives an error "Incorrect DATETIME value '2020-04-31'"

Quaternion · Accepted Answer · 2016-07-20 16:43:55Z

16

There's a few edge cases which need to be caught here by using the correct end date, if not items on the 31st are ignored:

SELECT * FROM util_audit 
WHERE DATED >= '2012-02-15 00:00:00' AND DATED <= '2012-03-31 23:59:59';

SELECT * FROM util_audit 
WHERE DATED BETWEEN '2012-02-15 00:00:00' AND '2012-03-31 23:59:59';

Or you could push the end date forward and use:

SELECT * FROM util_audit 
WHERE DATED >= '2012-02-15 00:00:00' AND DATED < '2012-04-01';

SELECT * FROM util_audit 
WHERE DATED BETWEEN '2012-02-15 00:00:00' AND '2012-04-01';

Just came across this myself so hope it helps if other people find this page.

edited Jul 20, 2016 at 16:43

Quaternion

10.4k6 gold badges54 silver badges104 bronze badges

answered Jul 14, 2013 at 9:02

James F

3462 silver badges8 bronze badges

1 Comment

Quaternion Over a year ago

I accidentally down voted this question. This is the date format I used! So did an edit, reversed my vote, and deleted the edit. Sorry for that.

karthik · Accepted Answer · 2012-11-23 03:41:18Z

7

As far as I know, dates in MySql are represented with the format yyyy-MM-dd hh:mm:ss hence you need to do this:

SELECT * FROM util_audit 
WHERE DATED >= '2012-02-15 00:00:00' AND DATED <= '2012-03-31 00:00:00';

Or even better:

SELECT * FROM util_audit 
WHERE DATED BETWEEN '2012-02-15 00:00:00' AND '2012-03-31 00:00:00';

edited Nov 23, 2012 at 3:41

karthik

17.9k71 gold badges83 silver badges125 bronze badges

answered Mar 30, 2012 at 1:04

Ezequiel Muns

7,78035 silver badges59 bronze badges

Collectives™ on Stack Overflow

mySQL dateTime range Query Issue

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related