日期查询
1.Convert转成String,在用Like查询。
select * from table1 where convert(varchar,yourtime,120) like ‘2017-06-30%’
2.Between
select * from table1 where yourtime between ‘2017-06-30 0:00:00’ and ‘2017-06-30 24:59:59’";
3 datediff()函数
select * from table1 where datediff(day,yourtime,‘2017-06-30’)=0
第一种方法应该适用与任何数据类型;
第二种方法适用String外的类型;
第三种方法则是为date类型定制的比较实用快捷的方法。
日期差比较
SELECT
TIME_TO_SEC(TIMEDIFF('2018-09-30 19:38:45', '2018-08-23 10:13:01')) AS DIFF_SECOND1, -- 秒
UNIX_TIMESTAMP('2018-09-30 19:38:45')-UNIX_TIMESTAMP('2018-08-23 10:13:01') AS DIFF_SECOND2, -- 秒
TIMESTAMPDIFF(SECOND,'2018-08-23 10:13:01','2018-09-30 19:38:45') AS DIFF_SECOND3, -- 秒
TIMESTAMPDIFF(MINUTE,'2018-08-23 10:13:01','2018-09-30 19:38:45') AS DIFF_MINUTE, -- 分
TIMESTAMPDIFF(HOUR,'2018-08-23 10:13:01','2018-09-30 19:38:45') AS DIFF_HOUR, -- 小时
TIMESTAMPDIFF(DAY ,'2018-08-23 10:13:01','2018-09-30 19:38:45') AS DIFF_DATE1, -- 天
DATEDIFF('2018-09-30 19:38:45','2018-08-23 10:13:01') AS DIFF_DATE2, -- 天
TIMESTAMPDIFF(MONTH,'2018-08-23 10:13:01','2018-09-25 19:38:45') AS DIFF_MONTH, -- 月
TIMESTAMPDIFF(YEAR,'2018-08-23 10:13:01','2020-07-25 19:38:45') AS DIFF_YEAR -- 年
FROM DUAL;
---------------------
作者:有趣的灵魂算我一个
来源:CSDN
原文:https://blog.csdn.net/weixin_42189233/article/details/80484088
版权声明:本文为博主原创文章,转载请附上博文链接!
作者:乐码乐
来源:CSDN
原文:https://blog.csdn.net/Denglishang/article/details/81980515
版权声明:本文为博主原创文章,转载请附上博文链接!