在SQL Developer中转换NVARCHAR2列为指定格式datetime的方法问询

要同时实现将字符串转换为可操作的日期类型和以指定格式展示，需要明确：Oracle的DATE/TIMESTAMP类型是内部数值存储，显示格式由会话参数或格式化函数控制，以下是具体实现步骤：

1. 将NVARCHAR2字符串转换为日期类型 #

原字符串格式为'29-OCT-23 05.30.03.942000000 PM'，先通过TO_TIMESTAMP解析为时间戳类型，按需转为DATE（精确到秒）或保留TIMESTAMP（带毫秒）：

转换为TIMESTAMP（保留毫秒精度） #

TO_TIMESTAMP(TIMEVALUE, 'DD-MON-RR HH.MI.SS.FF9 AM', 'NLS_DATE_LANGUAGE=ENGLISH')

• 格式掩码说明：DD-MON-RR匹配日-英文月缩写-两位年份；HH.MI.SS.FF9匹配小时.分钟.秒.9位毫秒
• 指定NLS_DATE_LANGUAGE=ENGLISH，避免因数据库语言设置导致月份解析失败

转换为DATE（仅保留到秒） #

如果不需要毫秒部分，可将TIMESTAMP转为DATE类型：

CAST(TO_TIMESTAMP(TIMEVALUE, 'DD-MON-RR HH.MI.SS.FF9 AM', 'NLS_DATE_LANGUAGE=ENGLISH') AS DATE)

2. 设置显示格式为目标样式 #

转换后的日期类型默认显示格式由会话参数控制，修改参数后，查询结果会自动以'29-10-23 05.30.03'格式展示，同时保留日期类型的可操作性：

针对DATE类型 #

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-RR HH.MI.SS';

针对TIMESTAMP类型 #

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MM-RR HH.MI.SS';

3. 完整查询示例 #

修改会话格式后，直接查询转换后的列即可，结果既是可操作的日期类型，又以目标格式显示：

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-RR HH.MI.SS';

SELECT 
    CAST(TO_TIMESTAMP(TIMEVALUE, 'DD-MON-RR HH.MI.SS.FF9 AM', 'NLS_DATE_LANGUAGE=ENGLISH') AS DATE) AS FORMATTED_DATE
FROM YOUR_TABLE;

如果需要在同一条查询中同时获取可操作的日期列和格式化字符串列，可使用：

SELECT 
    -- 可进行日期运算的DATE类型列
    CAST(TO_TIMESTAMP(TIMEVALUE, 'DD-MON-RR HH.MI.SS.FF9 AM', 'NLS_DATE_LANGUAGE=ENGLISH') AS DATE) AS DATE_COLUMN,
    -- 符合目标格式的字符串列
    TO_CHAR(TO_TIMESTAMP(TIMEVALUE, 'DD-MON-RR HH.MI.SS.FF9 AM', 'NLS_DATE_LANGUAGE=ENGLISH'), 'DD-MM-RR HH.MI.SS') AS FORMATTED_STRING
FROM YOUR_TABLE;

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