# Using variables inside the dbt\_utils.date\_spine macro

**URL:** <https://discourse.getdbt.com/t/using-variables-inside-the-dbt-utils-date-spine-macro/6566>\
**Category:** Help\
**Tags:** dbt-utils, date-and-time, variables\
**Created:** [February 2, 2023, 9:42pm UTC](https://discourse.getdbt.com/t/using-variables-inside-the-dbt-utils-date-spine-macro/6566 "2023-02-02T21:42:51Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![dschupak](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/dschupak/32/1484_2.png) [@dschupak](https://discourse.getdbt.com/u/dschupak)\
**Post date:** [February 2, 2023, 9:42pm UTC](https://discourse.getdbt.com/t/using-variables-inside-the-dbt-utils-date-spine-macro/6566/1 "2023-02-02T21:42:51Z")

</div>

I’m trying to create a date spine that where the first and last timestamps are taken from another model.

For example, if I have a table that shows the times that someone logs onto their computer, I want to create a date spine that will take the earliest logon time and the latest logon time. I can use `dbt_utils.date_spine` if I enter set dates for the beginning and end of the spine. I have tried a few different methods of setting the start and end timestamps using variables, such as `select min(logon_time) from activity_table` but they don’t work.

Does `dbt_utils.date_spine` have to take set dates as arguments or is there a way to use variables?

---

<div class="post-metadata">

**Author:** ![dschupak](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/dschupak/32/1484_2.png) [@dschupak](https://discourse.getdbt.com/u/dschupak)\
**Post date:** [February 2, 2023, 9:45pm UTC](https://discourse.getdbt.com/t/using-variables-inside-the-dbt-utils-date-spine-macro/6566/2 "2023-02-02T21:45:05Z")

</div>

For example, this works:  
` {{ dbt_utils.date_spine( datepart="second", start_date="2022-01-01", end_date="2022-01-01", ) }}`

This does not:

` {{ dbt_utils.date_spine( datepart="second", start_date=select min(logon_time) from activity_table, end_date=select max(logon_time) from activity_table, ) }}`

---

<div class="post-metadata">

**Author:** ![irvingpop](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/irvingpop/32/2259_2.png) [@irvingpop](https://discourse.getdbt.com/u/irvingpop)\
**Post date:** [April 15, 2023, 7:49pm UTC](https://discourse.getdbt.com/t/using-variables-inside-the-dbt-utils-date-spine-macro/6566/3 "2023-04-15T19:49:55Z")

</div>

Hey @dschupak hopefully you figured this out by now. I ran into the same issue and came up with a solution that works, but I don’t feel 100% great about it. Perhaps one of the experts here can chime in!

```auto
{% set get_first_sub_date %}
    select min(subscription_start_date)::string from {{ ref('stg_salesforce_sbqq_subscription') }}
{% endset %}
{%- set first_sub_date = dbt_utils.get_single_value(get_first_sub_date, default="'2017-01-01'") -%}

{% set get_last_sub_date %}
    select max(subscription_end_date)::string from {{ ref('stg_salesforce_sbqq_subscription') }}
{% endset %}
{%- set last_sub_date = dbt_utils.get_single_value(get_last_sub_date, default="'2026-12-31'") -%}

{{ dbt_utils.date_spine(
                        datepart="day",
                        start_date="'" + first_sub_date + "'",
                        end_date="'" + last_sub_date + "'"
                        )
}}

```

What particularly didn’t feel great was the parts where I had to cast the result to a `::string`, and then later when using it in date\_spine having to manually quote it.

---

<div class="post-metadata">

**Author:** ![ohitsmekatie](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/ohitsmekatie/32/2582_2.png) [@ohitsmekatie](https://discourse.getdbt.com/u/ohitsmekatie)\
**Post date:** [June 21, 2023, 4:09pm UTC](https://discourse.getdbt.com/t/using-variables-inside-the-dbt-utils-date-spine-macro/6566/4 "2023-06-21T16:09:51Z")

</div>

I’m curious if anyone found a different way to solve this problem without the casting/manually quoting! I have been running into this problem a lot when combined data about campaigns in salesforce against ad campaigns where they constantly need to be left joined to a date spine.

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [June 29, 2023, 3:29am UTC](https://discourse.getdbt.com/t/using-variables-inside-the-dbt-utils-date-spine-macro/6566/5 "2023-06-29T03:29:19Z")

</div>

> [@dschupak](#):
>
> This does not:
> 
> `{{ dbt_utils.date_spine( datepart="second", start_date=select min(logon_time) from activity_table, end_date=select max(logon_time) from activity_table, ) }}`

This is close! If you have a look at [how the date\_spine macro is implemented](https://github.com/dbt-labs/dbt-utils/blob/main/macros/sql/date_spine.sql), you’ll see that the first step is to run a datediff of `start_date` and `end_date`. This means that if you pass something in that is valid SQL, it will evaluate the valid SQL.

In this case, you just need to wrap it in brackets:

```auto
{{ dbt_utils.date_spine(
  datepart="second",
  start_date="(select min(logon_time) from ref('activity_table'))",
  end_date="(select max(logon_time) from ref('activity_table'))",    
) }}`

```

If you’re using a `ref`, you’ll need to quote them as I’ve done above, alternating single and double quotes.

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [June 29, 2023, 3:31am UTC](https://discourse.getdbt.com/t/using-variables-inside-the-dbt-utils-date-spine-macro/6566/6 "2023-06-29T03:31:44Z")

</div>

@ohitsmekatie in your case, you might prefer to pregenerate a date\_spine table with all days between, say, 2000 and 2050 (whatever dates make sense).

That’s my normal recommendation, but it isn’t very viable when you’re doing second-by-second spining like the original post was. Salesforce ad campaigns are probably at a more tractable granularity though!

---

<div class="post-metadata">

**Author:** ![johanndw](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/johanndw/32/4053_2.png) [@johanndw](https://discourse.getdbt.com/u/johanndw)\
**Post date:** [July 13, 2023, 1:15pm UTC](https://discourse.getdbt.com/t/using-variables-inside-the-dbt-utils-date-spine-macro/6566/7 "2023-07-13T13:15:43Z")

</div>

In BigQuery this did NOT work

```auto
{{ dbt_utils.date_spine(
  datepart="day",
  start_date="cast('2019-01-01' as date)",
  end_date="(select max(date) from ref('dim_date'))" 
) }}

Database Error in rpc request (from remote system.sql) Table-valued function not found: ref at [7:37]

```

But this DOES

```auto
{{ dbt_utils.date_spine(
  datepart="day",
  start_date="cast('2019-01-01' as date)",
  end_date="(select max(date) from " + ref('dim_date') | string + ")"
) }}

```

Maybe it helps someone in the future

---

<div class="post-metadata">

**Author:** ![Miroslaw](https://avatars.discourse-cdn.com/v4/letter/m/49beb7/32.png) [@Miroslaw](https://discourse.getdbt.com/u/Miroslaw)\
**Post date:** [January 22, 2024, 9:16am UTC](https://discourse.getdbt.com/t/using-variables-inside-the-dbt-utils-date-spine-macro/6566/8 "2024-01-22T09:16:52Z")

</div>

> [@johanndw](#):
>
> `datepart="day"`

It is really nice macro although it is limited. I was thinking to use this parameter as :  
datepart=“15 minutes”.  
Also macro does not have flag working days or is\_holiday like in calendar dimension, that is why calendar table can still be used as CTE expression for some cases better.

---

<div class="post-metadata">

**Author:** ![Adeolam](https://avatars.discourse-cdn.com/v4/letter/a/bb73d2/32.png) [@Adeolam](https://discourse.getdbt.com/u/Adeolam)\
**Post date:** [May 30, 2024, 4:29pm UTC](https://discourse.getdbt.com/t/using-variables-inside-the-dbt-utils-date-spine-macro/6566/9 "2024-05-30T16:29:03Z")

</div>

This helped me!! And I’m using Clickhouse. Thanks a lot

---

<div class="post-metadata">

**Author:** ![vshiv667](https://avatars.discourse-cdn.com/v4/letter/v/4bbf92/32.png) [@vshiv667](https://discourse.getdbt.com/u/vshiv667)\
**Post date:** [May 30, 2024, 5:38pm UTC](https://discourse.getdbt.com/t/using-variables-inside-the-dbt-utils-date-spine-macro/6566/10 "2024-05-30T17:38:20Z")

</div>

thank you this worked for the postgres/redshift adapter as well !

additionally for anyone looking to use this macro inside another macro and pass an argument to this can do so like this:

```auto
{% macro my_macro(my_filter_arg = '1=1') %}

with
date_spine as (
{{ dbt_utils.date_spine(
  datepart="day",
  start_date="cast('2019-01-01' as date)",
  end_date="(select max(date) from " + ref('dim_date') | string + ") + " where " + my_filter_arg | string + " )",
) }}
)

select * from date_spine
{% endmacro %}

```

---

<div class="post-metadata">

**Author:** ![aamna.akram](https://avatars.discourse-cdn.com/v4/letter/a/bc8723/32.png) [@aamna.akram](https://discourse.getdbt.com/u/aamna.akram)\
**Post date:** [January 23, 2025, 8:26pm UTC](https://discourse.getdbt.com/t/using-variables-inside-the-dbt-utils-date-spine-macro/6566/11 "2025-01-23T20:26:17Z")

</div>

{% set \_start\_date = “‘2020-01-01’” %}  
with import\_date as (  
{{ dbt\_utils.date\_spine(  
datepart=“day”,  
start\_date=“cast(”+\_start\_date+" as date)",  
end\_date=“current\_date()”  
)  
}}  
)