甲骨文日期时间函数错误

Question

我正在努力遵循sql并在日期部分不断收到错误

我收到的错误是：- ORA-01861：文字与格式字符串不匹配

  SELECT COUNT(*) 
  FROM  STGDBA.INTERACTIONS
  WHERE APP_ID='Home Depot'
  AND PEGA_ID ='0'
  AND TO_CHAR(to_date(END_TIME))BETWEEN TO_DATE('2012-AUG-01', 'YYYY-MON-DD')AND TO_DATE
('2012-AUG-31', 'YYYY-MON-DD')

这就是日期在数据库中的存储方式：- 2011-10-24 08：46：31.621

Answer 1

首先，切勿使用 VARCHAR2 数据类型将日期或时间戳数据存储在表中。 你应该始终使用正确的数据类型（date、timestamp、timestamp with time zone等）。 以错误的数据类型存储数据会导致性能问题（优化程序的基数估计值通常不太准确，并且索引列变得更加成问题）以及当某些应用程序无意中将格式错误的字符串存储在表中导致各种查询开始引发错误或执行计划中的细微更改导致错误突然开始（或停止）时难以发现的错误出现。

其次，假设表中每一行的每个end_time字符串（无论是否满足其他两个谓词）的格式YYYY-MM-DD HH24:MI:SS.FFF

SELECT COUNT(*) 
  FROM  STGDBA.INTERACTIONS
  WHERE APP_ID='Home Depot'
  AND PEGA_ID ='0'
  AND to_date(substr(END_TIME,1,length(end_time)-4), 
              'YYYY-MM-DD HH24:MI:SS' ) BETWEEN TO_DATE('2012-AUG-01', 'YYYY-MON-DD')
                                            AND TO_DATE('2012-AUG-31', 'YYYY-MON-DD')

您

始终希望将日期与日期以及字符串与字符串进行比较，因此您不希望用 to_char 括起来表达式的左侧。 调用 to_date 时，您总是希望指定显式格式掩码，这就是我在这里所做的。 而且date没有亚秒级精度，因此您必须去除秒的小数部分（如果您将end_time声明为timestamp(3)，这将不是问题）。