`dbt_date.get_date_dimension` not working with SQL Server 2019

[cnlee1702]

dbt_date.get_date_dimension macro not working with SQL Server. Tested with latest package versions (as of posting) and versions matching the dbt_date pinned commit from the tsql-utils repo.

dbt model

{{ dbt_date.get_date_dimension(
    "2020-01-01",
    "2024-01-01",
)}}

Error message

('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. (156) (SQLMoreResults); [42000] [Microsoft][ODBC Driver 17 for SQL Server]
[SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be termina
ted with a semicolon. (319); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ')'. (102)")

SQL Server version

Microsoft SQL Server 2019 (RTM-GDR) (KB5029377) - 15.0.2104.1 (X64) 
    Aug 16 2023 00:09:21 
    Copyright (C) 2019 Microsoft Corporation
    Developer Edition (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervisor)

dbt_project.yml dispatch configuration

dispatch:
  - macro_namespace: dbt_utils
    search_order: ['tsql_utils', 'dbt_utils']
  - macro_namespace: dbt_date
    search_order: ['tsql_utils', 'dbt_date']

Intended packages.yml

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.1
  - package: dbt-msft/tsql_utils
    version: 0.10.0
  - package: calogica/dbt_date
    version: 0.10.0

packages.yml testing dbt_date pinned commit

packages:
  - package: dbt-labs/dbt_utils
    version: [">=0.9.0", "<1.0.0"]
  - package: dbt-msft/tsql_utils
    version: 0.10.0
  - package: calogica/dbt_date
    version: 0.6.0

[prashant-ux23]

Having the same issue