在dbt的Jinja宏中从字符串参数提取年月日遇转换错误

错误原因分析 #

你遇到的报错转换''无效，说明传入的input_date参数是空字符串，或者宏渲染后CAST()中的值为空——比如调用宏时未正确传递参数，或者参数本身是空值，导致数据库无法将空字符串转换为日期类型。

修正后的宏实现 #

通用版（适配PostgreSQL等多数SQL方言） #

先处理空值情况，再执行日期转换和提取：

{% macro extract_year(input_date) %}
    DATE_PART('year', 
        CASE 
            WHEN {{ input_date }} IS NOT NULL AND {{ input_date }} != '' THEN CAST({{ input_date }} AS DATE)
            ELSE NULL
        END
    )
{% endmacro %}

{% macro extract_month(input_date) %}
    DATE_PART('month', 
        CASE 
            WHEN {{ input_date }} IS NOT NULL AND {{ input_date }} != '' THEN CAST({{ input_date }} AS DATE)
            ELSE NULL
        END
    )
{% endmacro %}

{% macro extract_day(input_date) %}
    DATE_PART('day', 
        CASE 
            WHEN {{ input_date }} IS NOT NULL AND {{ input_date }} != '' THEN CAST({{ input_date }} AS DATE)
            ELSE NULL
        END
    )
{% endmacro %}

MySQL/SQL Server专属版本（使用原生日期函数） #

如果你的数据库是MySQL或SQL Server，可使用更简洁的原生函数：

{% macro extract_year(input_date) %}
    YEAR(
        CASE 
            WHEN {{ input_date }} IS NOT NULL AND {{ input_date }} != '' THEN CAST({{ input_date }} AS DATE)
            ELSE NULL
        END
    )
{% endmacro %}

{% macro extract_month(input_date) %}
    MONTH(
        CASE 
            WHEN {{ input_date }} IS NOT NULL AND {{ input_date }} != '' THEN CAST({{ input_date }} AS DATE)
            ELSE NULL
        END
    )
{% endmacro %}

调用宏的注意事项 #

• 传入表字段时直接写字段名（无需加引号）：

  SELECT 
      {{ extract_year('order_date') }} AS order_year,
      {{ extract_month('order_date') }} AS order_month
  FROM orders
  

• 传入字符串字面量时，要额外用单引号包裹：

  SELECT {{ extract_year("'2024-05-20'") }} AS test_year
  

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