DATE列与SYSTIMESTAMP列比较结果异常问题咨询

表结构 #

create table demo(demo_id number,
                  created_date date,
                  mod_date systimestamp(6)
                 ); 

需求 #

返回包含两列的REF CURSOR：

• Demo_id
• Status列，取值规则：

  若created_date = mod_date则为"New"，否则为"Updated"

问题现象 #

执行以下SQL时，即使created_date和mod_date的日期时间部分视觉上一致，Status列始终显示"Updated"：

select demo_id ,case when created_date=mod_date then "New" else "update" end from demo; 

使用SQL Developer执行，已将NLS_DATE_FORMAT修改为DD/mm/yyyy hh24:mi:SS，mod_date存储的是systimestamp值。

问题根源 #

1. 数据类型精度不匹配：DATE类型仅精确到秒，而SYSTIMESTAMP(6)精确到微秒（6位小数）。直接比较时，Oracle会把DATE隐式转为TIMESTAMP，此时DATE的秒后补0，但mod_date实际存储的微秒部分可能不为0，导致两者不相等。
2. 字符串引用错误：Oracle中双引号"New"用于标识对象名（如表、列名），字符串常量必须用单引号'New'。

修复后的SQL #

方案一：截断时间戳到秒级别 #

SELECT 
    demo_id,
    CASE 
        WHEN created_date = TRUNC(mod_date, 'SS') THEN 'New' 
        ELSE 'Updated' 
    END AS status
FROM demo;

方案二：转换日期为时间戳后比较 #

SELECT 
    demo_id,
    CASE 
        WHEN CAST(created_date AS TIMESTAMP(0)) = TRUNC(mod_date, 'SS') THEN 'New' 
        ELSE 'Updated' 
    END AS status
FROM demo;

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