mysql Date and Time type query

最新推荐文章于 2025-02-06 18:50:32 发布

LF-DevJourney

最新推荐文章于 2025-02-06 18:50:32 发布

阅读量279

点赞数 1

CC 4.0 BY-SA版权

分类专栏：数据库 mysql

本文链接：https://blog.youkuaiyun.com/xy707707/article/details/80856358

数据库同时被 2 个专栏收录

10 篇文章

订阅专栏

mysql

6 篇文章

订阅专栏

本文介绍了MySQL中关于日期和时间的查询方法及注意事项，包括如何正确使用日期和时间格式进行查询，以及常见的错误示例。

mysql Date Time query

Date query

'2018'
'2018-09'
'2018-09-01'
2018-09
2018-09-01  // sometimes also not works
09
2018
201809
20180901

如 select * from avaliable_times where course_date = 2018;

mysql> update avaliable_times set still_valid="0" where teacher_id=95 and course_date=2018-07-09 and start_time=080000;
ERROR 1292 (22007): Incorrect date value: '2002' for column 'course_date' at row 1

Time query

'08:00:00'
'08'
08
0800
080000    // strongly recomened

如select * from avaliable_times where start_time in ('09:00:00', '08:00:00');

需要注意的是：2018-08-01可以查询，而08:00:00不能查询

mysql> select * from avaliable_times where start_time in (09:00:00, 08:00:00);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':00:00, 08:00:00)' at line 1