`sqlserver__dateadd()` does not work on Synapse
dataders opened this issue · 0 comments
dataders commented
for some reason sqlserver__dateadd
works on Azure SQL, but on Synapse, it comes up short by a day.
actual | expected |
---|---|
1900-01-02 00 00 00.0000000 | 1900-01-01 00 00 00.000 |
tsql-utils/macros/dbt_utils/cross_db_utils/dateadd.sql
Lines 1 to 13 in 730fa0d
with data as (
select * from "dbtsynapseci"."test"."data_dateadd"
)
select
case
when datepart = 'hour' then cast(
dateadd(
hour,
interval_length,
cast(from_time as datetime)
)
as
datetime2
)
when datepart = 'day' then cast(
dateadd(
day,
interval_length,
cast(from_time as datetime)
)
as
datetime2
)
when datepart = 'month' then cast(
dateadd(
month,
interval_length,
cast(from_time as datetime)
)
as
datetime2
)
when datepart = 'year' then cast(
dateadd(
year,
interval_length,
cast(from_time as datetime)
)
as
datetime2
)
else null
end as actual,
result as expected
from data