Repository: tobymao/sqlglot

Issue:
Snowflake TO_CHAR being replaced with CAST to text
**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.

```python
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
```sql
SELECT
    STRFTIME(RAW_ORDERS."ORDER_DATE", '%Y-%m') AS ORDER_YEAR_MONTH
FROM
    DEVELOPER.SCHEMA.RAW_ORDERS AS RAW_ORDERS
```

Actual output
```sql
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

Discussion:

Solution patch:
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index da4ec855a2..97dfe6350f 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -1856,3 +1856,17 @@ def groupconcat_sql(
             listagg.set("expressions", [f"{args}{self.sql(expression=expression.this)}"])
 
     return self.sql(listagg)
+
+
+def build_timetostr_or_tochar(args: t.List, dialect: Dialect) -> exp.TimeToStr | exp.ToChar:
+    this = seq_get(args, 0)
+
+    if this and not this.type:
+        from sqlglot.optimizer.annotate_types import annotate_types
+
+        annotate_types(this)
+        if this.is_type(*exp.DataType.TEMPORAL_TYPES):
+            dialect_name = dialect.__class__.__name__.lower()
+            return build_formatted_time(exp.TimeToStr, dialect_name, default=True)(args)
+
+    return exp.ToChar.from_arg_list(args)
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 837f8e7380..3013bf25dd 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -6,6 +6,7 @@
 from sqlglot.dialects.dialect import (
     Dialect,
     NormalizationStrategy,
+    build_timetostr_or_tochar,
     build_formatted_time,
     no_ilike_sql,
     rename_func,
@@ -21,19 +22,6 @@
     from sqlglot._typing import E
 
 
-def _build_timetostr_or_tochar(args: t.List) -> exp.TimeToStr | exp.ToChar:
-    this = seq_get(args, 0)
-
-    if this and not this.type:
-        from sqlglot.optimizer.annotate_types import annotate_types
-
-        annotate_types(this)
-        if this.is_type(*exp.DataType.TEMPORAL_TYPES):
-            return build_formatted_time(exp.TimeToStr, "oracle", default=True)(args)
-
-    return exp.ToChar.from_arg_list(args)
-
-
 def _trim_sql(self: Oracle.Generator, expression: exp.Trim) -> str:
     position = expression.args.get("position")
 
@@ -117,7 +105,7 @@ class Parser(parser.Parser):
             **parser.Parser.FUNCTIONS,
             "NVL": lambda args: build_coalesce(args, is_nvl=True),
             "SQUARE": lambda args: exp.Pow(this=seq_get(args, 0), expression=exp.Literal.number(2)),
-            "TO_CHAR": _build_timetostr_or_tochar,
+            "TO_CHAR": build_timetostr_or_tochar,
             "TO_TIMESTAMP": build_formatted_time(exp.StrToTime, "oracle"),
             "TO_DATE": build_formatted_time(exp.StrToDate, "oracle"),
             "TRUNC": lambda args: exp.DateTrunc(
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 32e0b55464..8473eaf4dc 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -6,6 +6,7 @@
 from sqlglot.dialects.dialect import (
     Dialect,
     NormalizationStrategy,
+    build_timetostr_or_tochar,
     binary_from_function,
     build_default_decimal_type,
     build_timestamp_from_parts,
@@ -463,6 +464,7 @@ class Parser(parser.Parser):
             "TRY_TO_TIMESTAMP": _build_datetime(
                 "TRY_TO_TIMESTAMP", exp.DataType.Type.TIMESTAMP, safe=True
             ),
+            "TO_CHAR": build_timetostr_or_tochar,
             "TO_DATE": _build_datetime("TO_DATE", exp.DataType.Type.DATE),
             "TO_NUMBER": lambda args: exp.ToNumber(
                 this=seq_get(args, 0),


Test patch:
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index a21dc4db0b..c5da2085f2 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -588,6 +588,16 @@ def test_snowflake(self):
                 "teradata": "TO_CHAR(x, y)",
             },
         )
+        self.validate_identity(
+            "TO_CHAR(foo::DATE, 'yyyy')", "TO_CHAR(CAST(CAST(foo AS DATE) AS TIMESTAMP), 'yyyy')"
+        )
+        self.validate_all(
+            "TO_CHAR(foo::TIMESTAMP, 'YYYY-MM')",
+            write={
+                "snowflake": "TO_CHAR(CAST(foo AS TIMESTAMP), 'yyyy-mm')",
+                "duckdb": "STRFTIME(CAST(foo AS TIMESTAMP), '%Y-%m')",
+            },
+        )
         self.validate_all(
             "SQUARE(x)",
             write={