Snowflake TO_CHAR being replaced with CAST to text

[asarama]

Before you file an issue

• Make sure you specify the "read" dialect eg. parse_one(sql, read="spark")
• Make sure you specify the "write" dialect eg. ast.sql(dialect="duckdb")
• Check if the issue still exists on main
  ✅

Fully reproducible code snippet
Please include a fully reproducible code snippet or the input sql, dialect, and expected output.

from sqlglot import parse_one

sf_query = """
SELECT
    TO_CHAR(RAW_ORDERS."ORDER_DATE", 'YYYY-MM') AS ORDER_YEAR_MONTH
FROM
    DEVELOPER.SCHEMA.RAW_ORDERS AS RAW_ORDERS
"""

sf_query = parse_one(query, dialect="snowflake")
duckdb_query = sf_query.sql(dialect="duckdb", pretty=True)

Expected output

SELECT
    STRFTIME(RAW_ORDERS."ORDER_DATE", '%Y-%m') AS ORDER_YEAR_MONTH
FROM
    DEVELOPER.SCHEMA.RAW_ORDERS AS RAW_ORDERS

Actual output

SELECT
    CAST(RAW_ORDERS."ORDER_DATE" AS TEXT) AS ORDER_YEAR_MONTH
FROM
    DEVELOPER.SCHEMA.RAW_ORDERS AS RAW_ORDERS

The ORDER_DATE column is a timestamp.

Happy to provide more info as needed. This might be quite challenging since TO_CHAR is quite flexible, but the usage patterns for timestamp conversions are fairly small.

https://docs.snowflake.com/en/sql-reference/functions/to_char
https://duckdb.org/docs/stable/sql/functions/dateformat.html