0

I am executing a raw query to fetch records between two dates. 

SELECT * FROM `tasks` 
WHERE '01-08-2017 11:00' 
BETWEEN `start_date` AND `end_date` AND `who`='vf00'

This query is returning results as below

start_date : 01-05-2017 10:30
end_date: 03-05-2017 10:00

It should not return any result since 01-08-2017 11:00 is not in between any range.

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
Zack
  • 171
  • 2
  • 12
  • your query is invalid. I doubt you have a column named `01-08-2017 11:00`. – Funk Forty Niner May 01 '17 at 14:06
  • 2
    01-08-2017 is actually between 01-05-2017 and 03-05-2017. Am I misunderstanding your question? – WillardSolutions May 01 '17 at 14:06
  • How come its between ? 08 is August month & 05 is May? – Zack May 02 '17 at 05:24
  • @Fred-ii- according to BETWEEN documentation you can either add column name or expression https://www.techonthenet.com/mysql/between.php – Zack May 02 '17 at 05:25
  • @Zack Thanks. I'll get back to you on that by running a few tests of my own. – Funk Forty Niner May 02 '17 at 10:08
  • @Zack I'm curious though. Is this more of a "why/behaviour" type of question? MySQL relies on `YYYY-mm-dd 00:00:00` as a datetime method, your `01-08-2017 11:00` won't work. MySQL's manual https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_between states that you may have to CAST those or use [`STR_TO_DATE()`](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date). – Funk Forty Niner May 02 '17 at 10:11
  • @Fred-ii-, solved by casting & datetime method YMD. Thanks – Zack May 02 '17 at 10:59
  • Right on @Zack and you're welcome. – Funk Forty Niner May 02 '17 at 11:04

0 Answers0