Date Time Issue in dbt Core with Macros

Help
, , ,
1

The problem I’m having

I am using dbt core and my target database is snowflake, and here is my get_week_date macro that I am using :

{% macro get_week_date(user_date) %}

    {% set dayofweek_query %}
        select dayname(date({{ user_date }})) as day_week, dayofweek(date({{ user_date }})) as day_num
    {% endset %}

    {% set week_day = run_query(dayofweek_query) %}
    {% do week_day.print_table() %}
{% endmacro %}

Whenever I am running this macro with dbt run-operation --args "{user_date : 2023-12-16}" get_week_date, I am getting the following result:

| DAY_WEEK | DAY_NUM |
| -------------- | -------------- |
| Thu              |                     4 |

However, this result is wrong, as 16th Dec of 2023 is Saturday and day of week is 6.
Snowflake query result:

image
image1099×231 13.8 KB

Also, I am getting the same output with 15th Dec.

Please guide me to solve this.

2

I have the feeling snowflake is interpreting this 2023-12-16 as an integer, so it is making the subtractions and taking the result as input.

Try

dbt run-operation --args "{user_date: '2023-12-16'}"
3

I tried that too, but it’s not working. same wrong date.