Oracle日期插入后查询格式不符及函数使用差异咨询

1. 直接查询DATE字段返回01-MAR-16的原因 #

Oracle的DATE类型本身不存储显示格式，它用内部数值（包含年月日时分秒信息）来存储日期数据。你看到的01-MAR-16，是当前会话的NLS_DATE_FORMAT参数控制的默认显示格式——这个参数默认值通常是DD-MON-RR，所以直接查询DATE字段时，客户端会按照这个格式把内部数值转成字符串展示，和你插入时使用的格式没有关系。

2. to_char与to_date返回结果不同的原因 #

这两个函数的本质完全不同：

• SELECT to_char(submission_date, 'yyyy-mm-dd') FROM submissions：to_char的作用是将DATE类型的数值转换为指定格式的字符串，返回结果是字符串类型，格式完全由你指定的yyyy-mm-dd控制，所以得到2016-03-01。
• SELECT to_date(submission_date, 'yyyy-mm-dd') FROM submissions：to_date的第一个参数要求是字符串类型，你传入DATE类型时，Oracle会先按当前NLS_DATE_FORMAT（即DD-MON-RR）把DATE转成字符串01-MAR-16，再尝试用yyyy-mm-dd格式解析这个字符串（Oracle日期解析有一定容错性，这里刚好能解析成功）。但解析后得到的还是DATE类型数值，最后又会按NLS_DATE_FORMAT转成01-MAR-16显示。这个操作完全多余，且如果格式不兼容会直接报错。

解决方案 #

如果希望默认查询DATE字段时显示yyyy-mm-dd，可以修改当前会话的参数：

ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd';

之后直接查询submission_date就会返回2016-03-01。或者每次查询都用to_char指定格式，这样不受会话参数影响。

内容的提问来源于stack exchange，提问作者kritika pandey