feat: mimic dateadd with date + day/week/month/year

[seruman]

Snowflake's DATEADD(<date_or_time_part>, <value>, <date_or_time_expr> ) seems to return DATE if;

• date_or_time_expr is DATE typed.
• and date_or_time_part is either DAY, WEEK, MONTH, YEAR.

with
    t1 as(
        select '2023-03-01' as literal, '2023-03-01'::date as date_valued
    )
select 
    dateadd('day', 3, literal::DATE) as c1, SYSTEM$TYPEOF(c1),
    dateadd('day', 3, date_valued) as c2, SYSTEM$TYPEOF(c2),

    dateadd('week', 3, literal::DATE) as c3, SYSTEM$TYPEOF(c3),
    dateadd('week', 3, date_valued) as c4, SYSTEM$TYPEOF(c4),

    dateadd('month', 3, literal::DATE) as c5, SYSTEM$TYPEOF(c5),
    dateadd('month', 3, date_valued) as c6, SYSTEM$TYPEOF(c6),

    dateadd('year', 3, literal::DATE) as c7, SYSTEM$TYPEOF(c7),
    dateadd('year', 3, date_valued) as c8, SYSTEM$TYPEOF(c8)
from t1
;

C1	SYSTEM$TYPEOF(C1)	C2	SYSTEM$TYPEOF(C2)	C3	SYSTEM$TYPEOF(C3)
2023-03-04	DATE[SB4]	2023-03-04	DATE[SB4]	2023-03-22	DATE[SB4]

C4	SYSTEM$TYPEOF(C4)	C5	SYSTEM$TYPEOF(C5)	C6	SYSTEM$TYPEOF(C6)
2023-03-22	DATE[SB4]	2023-06-01	DATE[SB4]	2023-06-01	DATE[SB4]

C7	SYSTEM$TYPEOF(C7)	C8	SYSTEM$TYPEOF(C8)
2026-03-01	DATE[SB4]	2026-03-01	DATE[SB4]

Since there's no way to ensure if a column's type is DATE in transformation phase, this transform casts result to DATE if only the operand has an explicit DATE cast with units known to return DATE.

-- snowflake
select dateadd('day', 3, '2023-03-01'::DATE);
-- 2023-03-04

-- duckdb, default
select CAST('2023-03-01' AS DATE) + INTERVAL 3 DAY;
-- 2023-03-04 00:00:00

-- duckdb, with transformation
select CAST(CAST('2023-03-01' AS DATE) + INTERVAL 3 DAY AS DATE);
-- 2023-03-04