Snowflake插入日期数据报错：数值'30D'无法识别，请求协助

错误原因 #

报错核心是字符串类型的日期值插入数字字段时发生隐式转换异常：D_YEAR字段定义为NUMBER(4,0)，但你用TO_CHAR(date, 'YYYY')返回字符串插入，虽理论上纯数字字符串可自动转数字，但会话日期格式异常、字段长度截断等情况会导致非数字字符串（如'30D'）被尝试转换为数字，触发报错。

此外SQL还存在其他潜在问题：

• D_DAYOFWEEK字段长度仅为TEXT(8)，但TO_CHAR(date, 'Day')返回的星期几名称（如'Wednesday'）长度为9，会被截断。
• D_SELLINGSEASON字段长度仅为TEXT(12)，但'New Year''s Day'长度为13，会被截断。
• 使用DATE_PART('dow', date)返回FLOAT类型插入NUMBER(1,0)字段，存在隐式转换风险。
• BOOLEAN字段用1/0返回，不符合类型规范，可能引发隐式转换问题。

修改后的SQL #

以下是修复所有问题后的完整SQL：

CREATE OR REPLACE TABLE date_dim (
  D_DATEKEY NUMBER(8,0) NOT NULL PRIMARY KEY,
  D_DATE TEXT(18),
  D_DAYOFWEEK TEXT(10), -- 扩展长度容纳最长星期几名称
  D_MONTH TEXT(9),
  D_YEAR NUMBER(4,0),
  D_YEARMONTHNUM NUMBER(6,0),
  D_YEARMONTH TEXT(7),
  D_DAYNUMINWEEK NUMBER(1,0),
  D_DAYNUMINMONTH NUMBER(2,0),
  D_DAYNUMINYEAR NUMBER(3,0),
  D_MONTHNUMINYEAR NUMBER(2,0),
  D_WEEKNUMINYEAR NUMBER(2,0),
  D_SELLINGSEASON TEXT(13), -- 扩展长度容纳完整节假日名称
  D_LASTDAYINWEEKFL BOOLEAN,
  D_LASTDAYINMONTHFL BOOLEAN,
  D_HOLIDAYFL BOOLEAN,
  D_WEEKDAYFL BOOLEAN
);

INSERT INTO date_dim(
  D_DATEKEY, D_DATE, D_DAYOFWEEK, D_MONTH, D_YEAR, 
  D_YEARMONTHNUM, D_YEARMONTH, D_DAYNUMINWEEK, D_DAYNUMINMONTH, 
  D_DAYNUMINYEAR, D_MONTHNUMINYEAR, D_WEEKNUMINYEAR, 
  D_SELLINGSEASON, D_LASTDAYINWEEKFL, D_LASTDAYINMONTHFL, 
  D_HOLIDAYFL, D_WEEKDAYFL
)
SELECT 
  TO_NUMBER(TO_CHAR(date, 'YYYYMMDD')) AS D_DATEKEY,
  TO_CHAR(date, 'Month DD, YYYY') AS D_DATE,
  TRIM(TO_CHAR(date, 'Day')) AS D_DAYOFWEEK, -- 去除自动补的空格
  TRIM(TO_CHAR(date, 'Month')) AS D_MONTH, -- 去除自动补的空格
  YEAR(date) AS D_YEAR, -- 直接返回数字，避免字符串转数字
  TO_NUMBER(TO_CHAR(date, 'YYYYMM')) AS D_YEARMONTHNUM,
  TO_CHAR(date, 'MonYYYY') AS D_YEARMONTH,
  DAYOFWEEK(date) - 1 AS D_DAYNUMINWEEK, -- 返回0-6的整数，替代FLOAT类型的DATE_PART
  DAY(date) AS D_DAYNUMINMONTH, -- 直接返回当月天数
  DAYOFYEAR(date) AS D_DAYNUMINYEAR, -- 直接返回当年天数
  MONTH(date) AS D_MONTHNUMINYEAR, -- 直接返回月份数字
  WEEKOFYEAR(date) AS D_WEEKNUMINYEAR, -- 直接返回周数
  CASE
    WHEN TO_CHAR(date, 'MMDD') = '0101' THEN 'New Year''s Day'
    WHEN TO_CHAR(date, 'MMDD') = '0704' THEN 'Independence Day'
    WHEN TO_CHAR(date, 'MMDD') = '1225' THEN 'Christmas Day'
    ELSE ''
  END AS D_SELLINGSEASON,
  CASE WHEN DAYOFWEEK(date) = 7 THEN TRUE ELSE FALSE END AS D_LASTDAYINWEEKFL, -- 返回BOOLEAN类型
  CASE WHEN DAY(date) = DAY(LASTDAYOFMONTH(date)) THEN TRUE ELSE FALSE END AS D_LASTDAYINMONTHFL, -- 简化月末判断逻辑
  CASE 
    WHEN TO_CHAR(date, 'MMDD') = '0101' THEN TRUE 
    WHEN TO_CHAR(date, 'MMDD') = '0704' THEN TRUE 
    WHEN TO_CHAR(date, 'MMDD') = '1225' THEN TRUE 
    ELSE FALSE
  END AS D_HOLIDAYFL, -- 返回BOOLEAN类型
  CASE WHEN DAYOFWEEK(date) IN (1,7) THEN FALSE ELSE TRUE END AS D_WEEKDAYFL -- 1=周日，7=周六，判断工作日
FROM (
  SELECT DATEADD(day, seq4(), '1997-12-30'::DATE) AS date -- 直接转换为DATE类型
  FROM table(generator(rowcount => 3652))
);

关键修复点说明 #

1. D_YEAR字段：使用YEAR(date)直接返回数字，彻底避免字符串到数字的隐式转换，解决核心报错问题。
2. 字段长度调整：扩展D_DAYOFWEEK和D_SELLINGSEASON的长度，避免名称截断。
3. 简化日期函数：用DAY()、MONTH()、DAYOFYEAR()等函数直接返回数字，替代TO_NUMBER(TO_CHAR(...))的冗余转换。
4. BOOLEAN类型规范：CASE语句直接返回TRUE/FALSE，符合字段类型定义，避免隐式转换风险。
5. 去除冗余转换：简化DATEADD的写法，去掉多余的CAST操作。

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