Oracle sql 日期时间戳筛选器

Question

我的 Oracle 表有 1,000 个观测值，以下 2 个变量：(两个字段都是 varchar(

ID   datecreate
ABC  24/12/2016 05:32:07

我想过滤日期。我尝试了以下方法，但无济于事...

select *
from table
where datecreate >= to_date('01/02/2018 00:00:00', 'dd/mm/yyyy hh24:mi:ss') and
datecreate <= to_date('28/02/2018 23:59:00', 'dd/mm/yyyy hh24:mi:ss')

我不断收到以下错误...

ORA-01843: not a valid month
01843. 00000 -  "not a valid month"
*Cause:    
*Action:

我错过了一些明显的东西吗？

谢谢

Answer 1

已经多次强调，在数据库中，DATE和TIME不应存储为字符串。然而，每天都有几个问题被提出，完全是由于这种不良做法而引起的。

请注意，在 Oracle 中，日期时间可以存储为DATE类型或TIMESTAMP(带或不带 TIMEZONE(。DATE类型还包含时间部分。

TO_DATE函数在必须处理日期字符串进行转换的情况下会帮助我们。使用日期时，最好以大多数 DBMS 理解的格式使用标准DATE文本'YYYY-MM-DD'。 (例如：- DATE '2018-02-28' (。

此外，当您可以使用DATE文本时，您不需要对此类边界情况使用 '28/02/2018 23:59:00' 或 TRUNC 函数。因此，您的查询可以重写为

SELECT *
FROM   yourtable
WHERE  TO_DATE(datecreate, 'dd/mm/yyyy hh24:mi:ss') >= DATE '2018-02-01'
   AND TO_DATE(datecreate, 'dd/mm/yyyy hh24:mi:ss')  < DATE '2018-02-28' + 1 

或者< DATE '2020-02-29'，如果你知道这是一个闰年。

如果你真的想考虑直到 23:59 分钟，那么TIMESTAMP文字可以与TO_TIMESTAMP函数一起使用。

.. AND  TO_TIMESTAMP(datecreate, 'dd/mm/yyyy hh24:mi:ss') <
        TIMESTAMP '2018-02-28 23:59:00'

Answer 2

默认NLS_DATE_FORMAT目前是 DD-MON-RR。因此，定义为字符串的 datecreate 列将被隐式转换为该格式。值 12 与 DEC 不同，因此错误。您可以使用正确的格式或将其转换为类似于以下内容的日期。

select *
from tbl
where to_date(datecreate, 'dd/mm/yyyy hh24:mi:ss') >= 
to_date('01/02/2016 00:00:00', 'dd/mm/yyyy hh24:mi:ss')
and to_date(datecreate, 'dd/mm/yyyy hh24:mi:ss') <= 
to_date('28/02/2018 23:59:00', 'dd/mm/yyyy hh24:mi:ss')