Oracle数据库按dd/mm/yy格式查询日期字段报错如何解决

三个报错写法的核心问题有两点：

• Oracle中字符串字面量必须使用单引号包裹，双引号仅用于标记表名、列名这类标识符，用双引号包裹日期字符串会被数据库识别为对象名，直接触发无效标识符报错
• 调用to_date()函数时必须显式指定日期格式掩码，不能仅传入日期字符串，否则会依赖数据库默认的NLS_DATE_FORMAT配置，极易出现解析错误；ANSI标准的DATE关键字仅支持YYYY-MM-DD格式的日期字面量，不支持dd/mm/yy格式的输入。

测试表结构 #

----------------------
| MyTable            |
----------------------
| id INT             |
| name VARCHAR(255)  |
| date_creation DATE |
----------------------

表内示例数据 #

---------------------------------
| id | name     | date_creation |
---------------------------------
| 1  | SomeText | 03/11/16      |
| 2  | Sample   | 04/11/16      |
| 3  | Sample1  | 13/07/22      |
| 4  | Sample2  | 11/07/22      |
---------------------------------

原错误写法问题说明 #

1. 第一种写法：用双引号包裹字符串，且to_date未指定格式

SELECT 
    id, name, date_creation
FROM
    MyTable            
WHERE 
    date_creation > to_date("01/07/22")

报错信息：ORA-00904: "01/07/22" : invalid identifier

2. 第二种写法：DATE关键字后接的日期不符合ANSI固定的YYYY-MM-DD格式，语法解析失败

SELECT 
    id, name, date_creation
FROM
    MyTable            
WHERE 
    date_creation > DATE "01/07/22"

报错信息：ORA-00936: missing expression

3. 第三种写法：直接用双引号包裹字符串和日期字段比较，字符串被识别为标识符

SELECT 
    id, name, date_creation
FROM
    MyTable            
WHERE 
    date_creation > "01/07/22"

报错信息：ORA-00904: "01/07/22" : invalid identifier

正确查询写法 #

要匹配dd/mm/yy格式的日期串，用to_date()显式指定格式掩码即可，写法如下：

SELECT 
    id, name, date_creation
FROM
    MyTable            
WHERE 
    date_creation > to_date('01/07/22', 'dd/mm/yy')

执行后会返回所有创建日期在2022年7月1日之后的行，即id为3、4的两条记录。

注意：如果日期串是4位年份（比如01/07/2022），只需要把格式掩码改成'dd/mm/yyyy'即可，不需要调整其他逻辑。

如果要使用ANSI日期字面量写法，必须把日期转成YYYY-MM-DD格式，示例如下：

SELECT 
    id, name, date_creation
FROM
    MyTable            
WHERE 
    date_creation > DATE '2022-07-01'

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