[CT-3254] Remove hard-coded adapter logic from tests for `date_spine` macros

Housekeeping

[X] I am a maintainer of dbt-core

Short description

@jwills asked within https://github.com/dbt-labs/dbt-core/discussions/8307#discussioncomment-7373642 if we can make it easier for adapter maintainers to specify their preferred date format within the tests for date_spine and supporting macros.

Currently, there is hardcoding of target.type == 'postgres' etc. whose logic that we want in adapter implementations rather than in dbt-core.

Acceptance criteria

there is no hard-coded adapter names (like target.type == 'postgres')
there is a clear and easy way for adapter maintainers to customize the test cases if their data platform doesn't support the syntax in the base adapter

Impact to Other Teams

This change directly affects dbt-bigquery, dbt-postgres, and dbt-redshift that may need to accommodate any change. The appropriate Impact: Adapters label has been added.

Will backports be required?

Backport to 1.7.latest. The backport 1.7.latest label has been added.

Context

See dbt-labs/dbt-core#8172, dbt-labs/dbt-core#8616, and https://github.com/dbt-labs/dbt-core/discussions/8307#discussioncomment-7373642 for background context.

See dbt-labs/dbt-adapters#192 for proposed cross-database to_date macro that is intended to make test customization on a per-adapter basis either completely unnecessary or exceedingly rare.

dbt-labs / dbt-adapters