如何避免Oracle在创建表时将日期类型转换为数值类型？

一、为什么你的CTAS语句生成的列显示为NUMBER？ #

你提供的CREATE TABLE AS SELECT语句中，SYSDATE本身是DATE类型，正常执行后agg_date列的实际数据类型应为DATE而非NUMBER。如果看到显示为NUMBER，大概率是以下情况：

• 所用客户端工具（如部分旧版SQL客户端）将DATE类型以Oracle内部存储的数值格式展示（Oracle DATE本质是7字节数值，但数据类型仍为DATE），并非真的修改了列类型。可通过以下语句确认实际类型：

DESCRIBE MY_SCHEMA.MY_TABLE;
-- 或查询系统视图
SELECT COLUMN_NAME, DATA_TYPE 
FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME = 'MY_TABLE' AND OWNER = 'MY_SCHEMA';

• 若列类型确实为NUMBER，检查是否在查询中对SYSDATE使用了数值转换函数（如TO_NUMBER()），或会话存在异常类型转换设置。

二、强制创建空白日期列的两种方法 #

方法1：直接定义列类型创建空表 #

这是最可靠的方式，直接显式指定DATE列类型，创建无数据的空表：

CREATE TABLE MY_SCHEMA.MY_TABLE (
    agg_level VARCHAR2(10),
    agg_date DATE
);

方法2：通过CTAS生成带空白DATE列的表 #

如果需要基于查询结构创建表但DATE列留空，可让查询返回NULL并显式转换为DATE类型，确保列类型正确：

CREATE TABLE MY_SCHEMA.MY_TABLE
AS
    SELECT
        'Day' AS agg_level,
        CAST(NULL AS DATE) AS agg_date
    FROM dual
    WHERE 1 = 0; -- 此条件让查询返回空结果，最终表无数据

若需要保留初始数据但DATE列为空，去掉WHERE 1=0即可：

CREATE TABLE MY_SCHEMA.MY_TABLE
AS
    SELECT
        'Day' AS agg_level,
        CAST(NULL AS DATE) AS agg_date
    FROM dual;

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