Raja Dhasan Raja Dhasan - 1 year ago 59
MySQL Question

Mysql <= is giving the wrong output

My table

starttime | endtime | id

10:30 11:30 1
11:30 12:30 2
14:30 16:30 3
15:30 16:30 4

If I wanted to select the id's between 10:30 to 12:30 I use this below command

select id
from table
where STR_TO_DATE(starttime,'%H:%i')>='10:30' and

This gives me only id 1 ,but not giving me 2 and If i change 12:30 to 12:40 it gives me 1 & 2.BUt I am using less or equal so it should give me both the id's right? Why its not working like that?

Answer Source

The problem is that you are trying to compare a date object against a string. To be precise, in the following expression

STR_TO_DATE(starttime,'%H:%i') >= '10:30'

STR_TO_DATE returns a date, but '10:30' is just a varchar (which coincidentally looks like a time). If you want to continue down this road, you should cast both sides of the comparison using STR_TO_DATE:

FROM table
WHERE STR_TO_DATE(starttime,'%H:%i') >= STR_TO_DATE('10:30','%H:%i') AND
      STR_TO_DATE(endtime,'%H:%i') <= STR_TO_DATE('12:30','%H:%i');

But a long term better approach would be to make your starttime and endtime columns either DATETIME or TIMESTAMP.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download