[Epic] Cross-database date macro

[dbeatty10]

This epic comprises the changes across dbt Labs repos necessary to implement a cross-database date macro as described in dbt-labs/dbt-adapters#192.

Implementation

There is no equivalent function specified in ISO/IEC 9075 (the SQL standard), so there is not a natural adapter-agnostic implementation from them.

However, many database vendors support a to_date function with syntax similar to the following:

to_date('{{ iso_8601_formatted_date }}', 'YYYY-MM-DD')

So we can utilize that as the base implementation and adapters can customize as-needed.

Utility

This function is also generally useful anytime someone wants to generate SQL to create an scalar DATE value by supplying an explicit year, month, day combo.

An expected use is supplying a start and end date to dbt.date_spine.

For example, #8825 describes how we'd like to automatically create a metricflow_time_spine model if the project defines any semantic objects, and that relies upon being able to do a cross-database dbt.date_spine given an explicit start and end date.

dbt-adapters

Beta Give feedback

1. [CT-3208] [Feature] Cross-database date macro dbt-adapters#192
   enhancement refinement user docs +3
   
2. Cross-database date macro dbt-adapters#191
   cla:yes user docs +2

dbt-postgres

Beta Give feedback

1. Cross-database date macro dbt-postgres#82
   +0
2. Cross-database date macro dbt-postgres#81
   cla:yes +1

dbt-redshift

Beta Give feedback

1. Cross-database date macro dbt-redshift#808
   +0
2. Cross-database date macro dbt-redshift#806
   cla:yes +1

dbt-snowflake

Beta Give feedback

1. Cross-database date macro dbt-snowflake#1013
   +0
2. Cross-database date macro dbt-snowflake#1011
   cla:yes +1

dbt-bigquery

Beta Give feedback

1. Cross-database date macro dbt-bigquery#1221
   +0
2. Cross-database date macro dbt-bigquery#1219
   cla:yes +1

dbt-spark

Beta Give feedback

1. Cross-database date macro dbt-spark#1031
   +0
2. Cross-database date macro dbt-spark#1030
   cla:yes +1

Documentation

Beta Give feedback

1. Cross-database date macro docs.getdbt.com#5427
   content dbt Core dbt-core v1.8 improvement +4
   
2. Include in migration guide for adapter maintainers

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.