SQL query for MYSQL for BETWEEN dates is not inclusive of the specified range?

Question

I am trying to create an SQL statement for MYSQL to retrieve records that fall between a date range. I would like the records to come back that includes the two dates that specify the range. At the moment this is not happening. Please can someone advise?

MY SQL-PHP:

 $fromDate = '21-SEP-2018';
 $toDate = '29-SEP-2018';

 $sql = "SELECT * FROM myTable WHERE zapDocUserId='$USER_ID' AND dateUpload BETWEEN \"$fromDate\" AND \"$toDate\" ORDER BY dateUpload DESC";
 $result = $mysqli -> query($sql);

The records that I get back do not include ones with dates 21-SEP-2018 and 29-SEP-2018. I would like it to be included.

'21-SEP-2018' is not MySQL date format. What is the datatype for your dateUpload column ? I suspect that it is string type. — Madhur Bhaiya
– Madhur Bhaiya, Commented Oct 30, 2018 at 7:43

Gordon Linoff · Accepted Answer · 2018-10-30 11:08:25Z

4

Do not use between for dates. The problem is the time component. Aaron Bertrand has a good blog about this (What do BETWEEN and the devil have in common?). Although geared to SQL Server, it applies to any database.

You seem to want:

SELECT *
FROM myTable
WHERE zapDocUserId = '$USER_ID' AND
     dateUpload >= '$fromDate' AND 
      dateUpload < '$toDate' + interval 1 day
ORDER BY dateUpload DESC;

Of course, this doesn't fix your date format. That is a different matter. But, you shouldn't merely fix the date format. You should learn how to pass parameters into queries, so the query is getting the right type to begin with. With placeholders, the query would look more like:

SELECT *
FROM myTable
WHERE zapDocUserId = ? AND
     dateUpload >= ? AND 
      dateUpload < ? + interval 1 day
ORDER BY dateUpload DESC;

answered Oct 30, 2018 at 11:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Michael Mulik Over a year ago

very hellpful answer, thank you very much, even in 2021!

Pablo Pazos Feb 6 at 3:23

Though using the DATE() function, using BETWEEN is safer.

Tim Biegeleisen · Accepted Answer · 2018-10-30 09:07:11Z

2

Your date literals are not valid in MySQL (or many other flavors of SQL). If you use a valid format, the query should work. Also, you should really be using prepared statements in your PHP code. Taking both of these into account, the following should work:

$fromDate = '2018-09-21';
$toDate = '2018-09-29';
$sql = "SELECT * FROM myTable WHERE zapDocUserId = ? AND dateUpload BETWEEN ? AND ? ";
$sql .= "ORDER BY dateUpload DESC";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('iss', $USER_ID, $fromDate, $toDate);
$stmt->execute();

This assumes that your dateUpload column is a proper date type column. If dateUpload is actually text, and you are storing your dates as e.g. 21-SEP-2018, then you would also have to make a call to STR_TO_DATE to convert the dates in your table as well:

$sql = "SELECT * FROM myTable WHERE zapDocUserId = ? AND ";
$sql .= "STR_TO_DATE(dateUpload, '%d-%b-%Y') BETWEEN ? AND ? ";
$sql .= "ORDER BY dateUpload DESC";

See here to read more about the rules for MySQL date and time literals.

edited Oct 30, 2018 at 9:07

answered Oct 30, 2018 at 7:46

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

7 Comments

jarlh Over a year ago

Doesn't RDB support those dates?

Tim Biegeleisen Over a year ago

@jarlh Are you just messing with me or is that an actual question?

jarlh Over a year ago

It was just a polite remark that those dates exist in the SQL world.

Álvaro González Over a year ago

@jarlh Tim's point is that anything you put in a VARCHAR column is nothing but text as long as MySQL is concerned. You can write code to parse such text and convert it to other types (integers, decimals, dates...) as long as the text is parseable but in general terms it's manual work that needs to be done explicitly.

jarlh Over a year ago

@ÁlvaroGonzález, totally agree.

|

Constantine Kurbatov · Accepted Answer · 2021-04-19 01:21:36Z

0

A very simple way to solve your problem is to use the CAST(<field> AS DATE) clause. That will remove the timing component from the query:

$fromDate = '2018-09-21';
$toDate = '2018-09-29';
$sql = "SELECT * FROM myTable WHERE zapDocUserId = ? AND CAST(dateUpload AS DATE) BETWEEN ? AND ? ";
$sql .= "ORDER BY dateUpload DESC";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('iss', $USER_ID, $fromDate, $toDate);
$stmt->execute();

In this example CAST(dateUpload AS DATE) — the way to remove the time component from the comparison and use a date only.

answered Apr 19, 2021 at 1:21

Constantine Kurbatov

93515 silver badges16 bronze badges

1 Comment

Bitwise DEVS Over a year ago

It gives me a correct starting row but the last row is 1 day behind

Collectives™ on Stack Overflow

SQL query for MYSQL for BETWEEN dates is not inclusive of the specified range?

3 Answers 3

2 Comments

7 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related