CODEWITHSUNDEEP
datevariablescastingdbt
JVA
JVA

Reputation: 131

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

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!

Upvotes: 2

Views: 9672

Answers (2)

Vivek Ramanathan
Vivek Ramanathan

Reputation: 470

Additionally, if you would like a timestamp in your desired time zone you can use:

'{{ run_started_at.astimezone(modules.pytz.timezone("America/New_York")) }}' as run_started_at_est

This would return a varchar field 2023-11-02 09:42:09.343605-04:00

For list of available time zones see:

https://gist.github.com/heyalexej/8bf688fd67d7199be4a1682b3eec7568

Upvotes: 0

Dat Nguyen
Dat Nguyen

Reputation: 316

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

Upvotes: 7

Related Questions