背景:
要求学校内各同学每天起床的平均时间,所以我们需要筛选出某学期内早上五点~早上十点之间的全部数据。
之前如果是使用SQL Server,则可以使用CONVERT函数,具体代码如下:
select * from v_come_out_record t where t.co_time between '2020-6-27' and '2020-11-27' and convert(varchar(5),t.co_time,114) between '05:00' and '10:00'
但是我也搬到MySQL执行了以后报错:
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version………………
所以在MySQL语句要修改一下:
select * from v_come_out_record t where t.co_time between '2020-6-27' and '2020-11-27' and DATE_FORMAT(t.co_time,'%H:%i:%s') between '05:00' and '10:00';
然后执行成功,最后输出结果如下: