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.
Mysql Like and between
Moderator: General Moderators
-
lokesh_kumar_s
- Forum Commoner
- Posts: 48
- Joined: Mon Apr 13, 2009 5:39 am
- Contact:
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Mysql Like and between
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]
[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.
Re: Mysql Like and between
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