Mysql Like and between

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
lokesh_kumar_s
Forum Commoner
Posts: 48
Joined: Mon Apr 13, 2009 5:39 am
Contact:

Mysql Like and between

Post 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.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Mysql Like and between

Post 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]
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Mysql Like and between

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply