CODEWITHSUNDEEP
phpmysqlsql
dean
dean

Reputation: 331

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

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.

Upvotes: 0

Views: 3097

Answers (3)

Constantine Kurbatov
Constantine Kurbatov

Reputation: 895

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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;

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

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.

Upvotes: 2

Related Questions