feat(duckdb): Add transpilation support for NEXT_DAY function

[fivetran-amrutabhimsenayachit]

Snowflake's NEXT_DAY(date, day_name) function is not supported in DuckDB. SQLGlot passes the NEXT_DAY function through unchanged, causing a runtime error in DuckDB.

Duckdb Error:

ERROR: Catalog Error: Scalar Function with name next_day does not exist!
Did you mean "nextval"?

DuckDB lacks a native NEXT_DAY function, requiring synthesis using:

• ISODOW(date) for ISO day of week (1=Monday through 7=Sunday)
• Date arithmetic with INTERVAL
• CAST to ensure DATE return type

After:

Transpilation:
python3 -c "import sqlglot; print(sqlglot.transpile(\"SELECT NEXT_DAY(DATE '2024-01-01', 'Monday') AS next_monday_from_date, NEXT_DAY(DATE '2024-01-01', 'mo') AS next_monday_abbrev, NEXT_DAY(TIMESTAMP '2024-01-01 10:30:45', 'Friday') AS next_friday_from_timestamp, NEXT_DAY(DATE '2024-01-05', 'Sunday') AS next_sunday, NEXT_DAY(DATE '2024-01-05', 'su') AS next_sunday_abbrev, NEXT_DAY(DATE '2024-01-05', 'Wednesday') AS next_wednesday, NEXT_DAY(DATE '2024-01-05', 'we') AS next_wednesday_abbrev, NEXT_DAY(DATE '2024-02-29', 'Thursday') AS leap_year_next_thursday, NEXT_DAY(NULL, 'Monday') AS null_date, NEXT_DAY(DATE '2024-01-01', NULL) AS null_day_name\", read='snowflake', write='duckdb')[0])"
-->
SELECT CAST(CAST('2024-01-01' AS DATE) + INTERVAL ((1 - ISODOW(CAST('2024-01-01' AS DATE)) + 6) % 7 + 1) DAY AS DATE) AS next_monday_from_date, CAST(CAST('2024-01-01' AS DATE) + INTERVAL ((1 - ISODOW(CAST('2024-01-01' AS DATE)) + 6) % 7 + 1) DAY AS DATE) AS next_monday_abbrev, CAST(CAST('2024-01-01 10:30:45' AS TIMESTAMP) + INTERVAL ((5 - ISODOW(CAST('2024-01-01 10:30:45' AS TIMESTAMP)) + 6) % 7 + 1) DAY AS DATE) AS next_friday_from_timestamp, CAST(CAST('2024-01-05' AS DATE) + INTERVAL ((7 - ISODOW(CAST('2024-01-05' AS DATE)) + 6) % 7 + 1) DAY AS DATE) AS next_sunday, CAST(CAST('2024-01-05' AS DATE) + INTERVAL ((7 - ISODOW(CAST('2024-01-05' AS DATE)) + 6) % 7 + 1) DAY AS DATE) AS next_sunday_abbrev, CAST(CAST('2024-01-05' AS DATE) + INTERVAL ((3 - ISODOW(CAST('2024-01-05' AS DATE)) + 6) % 7 + 1) DAY AS DATE) AS next_wednesday, CAST(CAST('2024-01-05' AS DATE) + INTERVAL ((3 - ISODOW(CAST('2024-01-05' AS DATE)) + 6) % 7 + 1) DAY AS DATE) AS next_wednesday_abbrev, CAST(CAST('2024-02-29' AS DATE) + INTERVAL ((4 - ISODOW(CAST('2024-02-29' AS DATE)) + 6) % 7 + 1) DAY AS DATE) AS leap_year_next_thursday, CAST(NULL AS DATE) AS null_date, CAST(NULL AS DATE) AS null_day_name

Duckdb:
duckdb -c "SELECT CAST(CAST('2024-01-01' AS DATE) + INTERVAL ((1 - ISODOW(CAST('2024-01-01' AS DATE)) + 6) % 7 + 1) DAY AS DATE) AS next_monday_from_date, CAST(CAST('2024-01-01' AS DATE) + INTERVAL ((1 - ISODOW(CAST('2024-01-01' AS DATE)) + 6) % 7 + 1) DAY AS DATE) AS next_monday_abbrev, CAST(CAST('2024-01-01 10:30:45' AS TIMESTAMP) + INTERVAL ((5 - ISODOW(CAST('2024-01-01 10:30:45' AS TIMESTAMP)) + 6) % 7 + 1) DAY AS DATE) AS next_friday_from_timestamp, CAST(CAST('2024-01-05' AS DATE) + INTERVAL ((7 - ISODOW(CAST('2024-01-05' AS DATE)) + 6) % 7 + 1) DAY AS DATE) AS next_sunday, CAST(CAST('2024-01-05' AS DATE) + INTERVAL ((7 - ISODOW(CAST('2024-01-05' AS DATE)) + 6) % 7 + 1) DAY AS DATE) AS next_sunday_abbrev, CAST(CAST('2024-01-05' AS DATE) + INTERVAL ((3 - ISODOW(CAST('2024-01-05' AS DATE)) + 6) % 7 + 1) DAY AS DATE) AS next_wednesday, CAST(CAST('2024-01-05' AS DATE) + INTERVAL ((3 - ISODOW(CAST('2024-01-05' AS DATE)) + 6) % 7 + 1) DAY AS DATE) AS next_wednesday_abbrev, CAST(CAST('2024-02-29' AS DATE) + INTERVAL ((4 - ISODOW(CAST('2024-02-29' AS DATE)) + 6) % 7 + 1) DAY AS DATE) AS leap_year_next_thursday, CAST(NULL AS DATE) AS null_date, CAST(NULL AS DATE) AS null_day_name"
┌──────────────────────┬────────────────────┬──────────────────────┬─────────────┬────────────────────┬────────────────┬──────────────────────┬───────────────────────┬───────────┬───────────────┐
│ next_monday_from_d…  │ next_monday_abbrev │ next_friday_from_t…  │ next_sunday │ next_sunday_abbrev │ next_wednesday │ next_wednesday_abb…  │ leap_year_next_thur…  │ null_date │ null_day_name │
│         date         │        date        │         date         │    date     │        date        │      date      │         date         │         date          │   date    │     date      │
├──────────────────────┼────────────────────┼──────────────────────┼─────────────┼────────────────────┼────────────────┼──────────────────────┼───────────────────────┼───────────┼───────────────┤
│ 2024-01-08           │ 2024-01-08         │ 2024-01-05           │ 2024-01-07  │ 2024-01-07         │ 2024-01-10     │ 2024-01-10           │ 2024-03-07            │ NULL      │ NULL          │
└──────────────────────┴────────────────────┴──────────────────────┴─────────────┴────────────────────┴────────────────┴──────────────────────┴───────────────────────┴───────────┴───────────────┘

[github-actions]

SQLGlot Integration Test Results

Comparing:

• this branch (sqlglot:RD-1069370-next-day, sqlglot version: RD-1069370-next-day)
• baseline (main, sqlglot version: 28.6.1.dev1)

⚠️ Limited to dialects: snowflake, duckdb, bigquery

By Dialect

dialect	main	sqlglot:RD-1069370-next-day	difference	links
bigquery -> bigquery	2576/2621 passed (98.3%)	2576/2621 passed (98.3%)	No change	full result / delta
bigquery -> duckdb	1845/2620 passed (70.4%)	1845/2620 passed (70.4%)	No change	full result / delta
duckdb -> duckdb	4003/4003 passed (100.0%)	4003/4003 passed (100.0%)	No change	full result / delta
snowflake -> duckdb	599/847 passed (70.7%)	599/847 passed (70.7%)	No change	full result / delta
snowflake -> snowflake	847/847 passed (100.0%)	847/847 passed (100.0%)	No change	full result / delta

Overall

main: 10938 total, 9870 passed (pass rate: 90.2%), sqlglot version: 28.6.1.dev1

sqlglot:RD-1069370-next-day: 10938 total, 9870 passed (pass rate: 90.2%), sqlglot version: RD-1069370-next-day

Difference: No change

[georgesittas]

Well done @fivetran-amrutabhimsenayachit, looks great.

[fivetran-amrutabhimsenayachit]

@georgesittas , I have addressed all the review comments for both NEXT_DAY and PREVIOUS_DAY and consolidated both the implementations in a single helper function which can be accessed from TRANSFORMS.

[georgesittas]

Thanks, taking another look.

[georgesittas]

nice 👍