Oracle关于日期字段的查询

最近碰到了这个问题，被啪啪啪的打脸，写下来记录自己的黑历史。流水账，着急的直接穿越到文末看结论。

问题：查询某表在一月份到三月份的数据。

举例：student（id varchar(20),name varchar(20), cretae_time)，求1-1到3-31号报道的学生总数。

过程：

自己刚开始写的sql语句如下：select count(*) from student where create_time between to_date('2019/01/01','yyyy/mm/dd') and to_date('2019/03/31','yyyy/mm/dd');

自认为没有什么问题，提交数据，被问到：Oracle比较日期类型直接用between...and不行吗？举个例子：select count(*) from student  s where s.create_time between '2019/01/01' and '2019/03/31';如果修改为：select count(*) from student s where to_char('s.create_time','yyyy/mm/dd') between '2019/01/01' and '2019/03/31';可以吗？

为了XX，自己特意去试了试，还特意测试了一下mysql，回复到：对的，需要使用tochar，或者to_date函数，不然会出现“文字与格式字符不匹配”错误，查不出结果。mysql则可以直接比较，不需要用到函数转换。

接着，莫名其妙的说让我把文件改下，改成用to_char('s.create_time','yyyy/mm/dd')这种形式的。......一堆疑问就出来了，这人有问题？to_char或者to_date效果不一样嘛？人家道：这样日期转字符更紧凑一些，没有能或不能。而且我这样边界值是不对的。

为了反驳，证明真理是掌握在我这里的，又向度娘跟同事询问一波。了解到的有：between ...and是包含边界值的，那岂不是说我的sql没问题？？？还有就是to_char,to_date都能作日期查询的使用，涉及到了效率问题，又一波oracle sql优化to_date和to_char 的使用的百度 ，接着就想去反驳：我的没问题，between and 是包含边界值的，to_char,to_date都可以用，而且to_date的效率貌似更高！（但有的说要看情况，比如加索引的情况，数据的多少等）。

自己又胆小，建立个数据库测试下，....我和我的小伙伴都惊呆了，竟然，边界值包含不了，没有3月31号的数据，又一波百度。发现了问题，大概就是隐式转换，显示转换什么鬼的，也没看懂。然后又测试了下to_char，和to_date的效率，数据量较少，to_date的效率更点。

那就这样吧，老老实实，周一改sql接着查。参考链接为：https://blog.youkuaiyun.com/weixin_34123613/article/details/89563103

结论：

Oracle关于日期字段的查询，to_char和to_date两个都能用，但是需要注意：to_date会缩小范围，不包括边界值。to_char会降低效率。between ...and虽然包括边界值，但在时间类型上不适用。

另外，

 

to_date(‘2007-06-12 10:00:00’, ‘yyyy-mm-dd hh24:mi:ss’)

当省略HH、MI和SS对应的输入参数时，Oracle使用0作为DEFAULT值。如果输入的日期数据忽略时间部分，Oracle会将时、分、秒部分都置为0，也就是说会取整到日。

同样，忽略了DD参数，Oracle会采用1作为日的默认值，也就是说会取整到月。

但是，不要被这种“惯性”所迷惑，如果忽略MM参数，Oracle并不会取整到年，取整到当前月。

注意：

1.在使用Oracle的to_date函数来做日期转换时，可能会直觉地采用“yyyy-MM-dd HH:mm:ss”的格式作为格式进行转换，但是在Oracle中会引起错误：“ORA 01810 格式代码出现两次”。如：select to_date(‘2005-01-01 13:14:20’,’yyyy-MM-dd HH24:mm:ss’) from dual;

原因是SQL中不区分大小写，MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟。select to_date(‘2005-01-01 13:14:20’,’yyyy-MM-dd HH24:mi:ss’) from dual;

2.另要以24小时的形式显示出来要用HH24

select to_char(sysdate,’yyyy-MM-dd HH24:mi:ss’) from dual;//mi是分钟

select to_char(sysdate,’yyyy-MM-dd HH24:mm:ss’) from dual;//mm会显示月份