select data from mysql using two column ranges

Question

I have data like this

sno name    date        time

1   Irona   2016-01-01  10:00:00
2   Meona   2016-01-02  21:00:00
3   Lessa   2016-01-02  8:00:00
4   Airik   2016-01-03  10:00:00

I m trying query like this

SELECT * FROM `appointment` where (date <= '2016-01-02' and time >= '21:00:00') and (date >= '2016-01-03' and time >= '10:00:00')

I want those appointment which are between 2016-01-02 and 2016-01-03 and also between 9 pm to 10 am

Answer 1

Try this:

SELECT * 
FROM `appointment` A
WHERE STR_TO_DATE(CONCAT(A.date, ' ', A.time), '%Y-%m-%d %H:%i:%s') BETWEEN '2016-01-02 21:00:00' AND '2016-01-03 10:00:00'

Answer 2

Query date <= '2016-01-02' and date >= '2016-01-03' returns nothing. you should change it to date >= '2016-01-02' and date <= '2016-01-03'

SELECT * FROM `appointment` 
where date >= '2016-01-02' and date <= '2016-01-03' 
and time <= '21:00:00' and time >= '10:00:00'

Answer 3

I will do it like this:

...
WHERE
to_char(date, '%Y%m%d') between '20160102' and '20160103'
AND to_char(timedate, '%H') between '10' and '21'