create a variable to see date when model was run with dbt

Question

I need to add to my dbt model a column which shows the current date when the model is run.

I have tried {{ run_started_at.strftime("%Y-%m-%d") }} by adding it directly to my model and also have tried on setting a variable called date and creating this piece of code ( {% set report_date = dbt_utils.get_query_results_as_dict("select dateadd(day,-1,current_date()) as col")["COL"][0] %} ) but the problem is that the dates, as we are today at 2021-12-14, the result for this column is 1995 ( this is the substraction of 2021 - 12 - 14). So, does anybody now a way to cast this variable to a date? thanks!

Answer 1

Well I think I know the secret now

Please remember dbt is a tool for code-generating-like

In your case, I guess you must to wrap your jinja code in a string quote

WRONG:

SELECT {{ run_started_at.strftime("%Y-%m-%d") }} as YourColumn
--compiled: SELECT 2021-12-14 as YourColumn
--YourColumn=1995

CORRECT:

SELECT '{{ run_started_at.strftime("%Y-%m-%d") }}' as YourColumn
--compiled: SELECT '2021-12-14' as YourColumn
--YourColumn=2021-12-14