CODEWITHSUNDEEP
jinja2dbt
Catarina Ribeiro
Catarina Ribeiro

Reputation: 646

How to use a variable concatenated with a string in dbt_utils.dateadd in dbt?

I have set a variable called year. I want to use dateadd function but instead of using the from_date_or_timestamp like the following example

{{ dbt_utils.dateadd(datepart='day', interval=1, from_date_or_timestamp="'2017-01-01'") }}

I want to use the variable year concatenated with the month and the day, something like this

{{ dbt_utils.dateadd(datepart='day', interval=1, from_date_or_timestamp= year ~ '-01-01') }}

But I can't get this to work... Can anyone help me?

Upvotes: 2

Views: 3490

Answers (1)

Aleix CC
Aleix CC

Reputation: 2099

A workaround to this could be the following:

{% set year = 2021 %}

select 
  {{ dbt_utils.dateadd(
    datepart = 'day',
    interval = 1,
    from_date_or_timestamp = "'" ~ year ~ "-01-01'"
    )
  }} as date_add_field

Since you need the input of from_date_or_timestamp to be a string, adding a trailing ' and then -01-01', both wrapped in "", you will make it work. The compiled SQL will look like the following (I am using Snowflake, your compiled SQL might differ):

select 
  dateadd(day, 1, '2021-01-01') as date_add_field

Upvotes: 5

Related Questions