Oracle SQL中将DATE转为Excel式数字并拼接字符串

嘿，这个问题我熟！你想要的其实是把Oracle里的DATE类型转换成Excel的日期序列号（就是Excel把日期转成的数字），然后和你的VARCHAR2字段拼接对吧？

先给你说清楚为什么你现在的结果是XS129755903824-DEC-15：当你直接把VARCHAR2和DATE类型拼接时，Oracle会自动把DATE转成默认的字符串格式（由NLS_DATE_FORMAT参数控制，你的环境里应该是DD-MON-RR这类），所以才会出现带月份缩写的结果，而不是你要的数字。

核心思路：把DATE转成Excel日期序列号 #

Excel的日期序列号是从1899-12-30开始计算的天数（这里要注意Excel有个历史bug，把1900年当成闰年，不过后面再说这个），所以我们只需要计算你的payment_date和DATE '1899-12-30'的差值，取整后就是对应的Excel数字，再和A字段拼接就行。

直接可用的SQL语句 #

SELECT DISTINCT
    A,
    A || TO_CHAR(ROUND(payment_date - DATE '1899-12-30')) AS concatenated_result
FROM your_table_name;

比如你提到的期望结果XS129755903842362，对应的payment_date应该是2015-12-24，你可以单独验证这个计算：

SELECT ROUND(DATE '2015-12-24' - DATE '1899-12-30') FROM DUAL;

执行后会得到42362，正好是你想要的那个数字部分。

特殊情况处理：Excel的1900闰年bug #

Excel错误地认为1900年是闰年，所以它会把不存在的1900-02-29当成有效日期，序列号为60。如果你的业务数据涉及1900年2月28日之前的日期，需要额外调整：

SELECT DISTINCT
    A,
    A || TO_CHAR(
        CASE
            WHEN payment_date BETWEEN DATE '1900-01-01' AND DATE '1900-02-28'
                THEN ROUND(payment_date - DATE '1899-12-30')
            WHEN payment_date > DATE '1900-02-28'
                THEN ROUND(payment_date - DATE '1899-12-30') + 1
            ELSE ROUND(payment_date - DATE '1899-12-30')
        END
    ) AS concatenated_result
FROM your_table_name;

不过一般业务场景里很少用到这么早的日期，这个可以根据你的实际需求选择是否添加。

额外提示：处理NULL值 #

如果payment_date可能为空，建议用NVL避免拼接结果变成NULL：

A || NVL(TO_CHAR(ROUND(payment_date - DATE '1899-12-30')), '') AS concatenated_result

内容的提问来源于stack exchange，提问作者Shashi Shankar Singh