Oracle 19c下转换datetime为timestamp并取较大值的实现问题

转换未生效问题排查 #

Oracle的DATE（即你所说的datetime）类型本身不存储展示格式，你看到的DD/MM/YY HH24:MI:SS格式是客户端NLS参数控制的展示效果：

• 若你的字段本身就是DATE类型，CAST(字段 AS TIMESTAMP)执行后字段实际类型已经变为TIMESTAMP，只是客户端可能仍沿用DATE类型的默认展示格式，可通过语句SELECT DUMP(CAST(DAT_CRE AS TIMESTAMP)) FROM ACTI WHERE ROWNUM = 1验证实际类型。
• 若你的字段实际是VARCHAR2字符串类型存储的时间值，你之前转换失败大概率是因为格式符写错：时间格式中代表分钟的是MI，你写的MM是月份的格式符，正确转换写法为CAST(TO_DATE(DAT_CRE, 'DD/MM/YY HH24:MI:SS') AS TIMESTAMP)。

取两个时间较大值的实现 #

直接使用Oracle内置GREATEST()函数即可，该函数支持传入多个日期/时间类型参数，返回最大值。修改后的完整SQL如下：

SELECT 
    CODE_ACT_PROD, 
    LIB, 
    CAST(DAT_CRE AS TIMESTAMP) AS DATE_CRE_TIMESTAMP,
    CAST(DAT_MOD AS TIMESTAMP) AS DATE_MOD_TIMESTAMP,
    -- 直接返回两个时间中较大值的TIMESTAMP类型结果
    CAST(GREATEST(DAT_CRE, DAT_MOD) AS TIMESTAMP) AS MAX_DATE_TIMESTAMP
FROM ACTI
WHERE CODE_ACT_PROD
IN (
    SELECT CODE_ACT_PROD
    FROM ART_COM
    WHERE ETAT = 0
)

注意事项 #

如果DAT_CRE或DAT_MOD存在NULL值，GREATEST()函数会返回NULL，可结合NVL()函数处理空值，示例：GREATEST(NVL(DAT_MOD, DAT_CRE), DAT_CRE)，代表如果DAT_MOD为空则默认取DAT_CRE作为比较值

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