Page 1 of 1

Mysql Like and between

Posted: Fri Jul 16, 2010 8:27 am
by lokesh_kumar_s
i want records which is having its time between 10:00 to 02:00. i have used datetime datatype in mysql.
the query for selecting particular time entry will be like this.

SELECT * FROM `AttendanceScanDetails` WHERE `DateTime` LIKE '%10:30%'

i wanted to select some thing like this

SELECT * FROM `AttendanceScanDetails` WHERE `DateTime` BETWEEN '%10:00%' AND '%20:00%'

please help.

Re: Mysql Like and between

Posted: Fri Jul 16, 2010 1:13 pm
by AbraCadaver
You can use TIME, TIME_FORMAT or DATE_FORMAT in a variety of ways:

[text]SELECT * FROM `AttendanceScanDetails` WHERE TIME_FORMAT(`DateTime`,'%H%i') BETWEEN '1000' AND '2000'[/text]

Re: Mysql Like and between

Posted: Sat Jul 17, 2010 3:21 am
by VladSun
If you have a lot of records, it will be a good idea to split the DateTime column into two columns - DATE and TIME types, thus your queries may use indexes on these columns.