Repository: tobymao/sqlglot

Issue:
Date casting function does not transpile correctly from Spark SQL to Snowflake SQL
Reproducible code:
```
import sqlglot
sql = """select to_date(some_column, "yyyy-MM-dd) from df"""
print(sqlglot.parse_one(sql, "spark").sql("snowflake"))
```

The output is:
```
SELECT TO_DATE(some_column, 'yyyy-mm-DD') FROM df
```

The problem is that the `to_date()` function behaves differently in Spark and in Snowflake. In Spark, [it returns NULL if the input does not match the format provided](https://spark.apache.org/docs/latest/api/sql/index.html#to_date). In Snowflake, [it raises an error if the input does not match the format provided](https://docs.snowflake.com/en/sql-reference/functions/to_date). The correct transpiled function in Snowflake should be [`TRY_TO_DATE()`](https://docs.snowflake.com/en/sql-reference/functions/try_to_date), which returns a NULL value instead of raising an error.

Hints from issue comments:

Solution patch:
diff --git a/sqlglot/dialects/databricks.py b/sqlglot/dialects/databricks.py
index ed5e64235e..12e9fb3a27 100644
--- a/sqlglot/dialects/databricks.py
+++ b/sqlglot/dialects/databricks.py
@@ -7,6 +7,7 @@
     date_delta_sql,
     build_date_delta,
     timestamptrunc_sql,
+    build_formatted_time,
 )
 from sqlglot.dialects.spark import Spark
 from sqlglot.tokens import TokenType
@@ -46,6 +47,7 @@ class Parser(Spark.Parser):
             "DATEDIFF": build_date_delta(exp.DateDiff),
             "DATE_DIFF": build_date_delta(exp.DateDiff),
             "GET_JSON_OBJECT": _build_json_extract,
+            "TO_DATE": build_formatted_time(exp.TsOrDsToDate, "databricks"),
         }
 
         FACTOR = {
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index 1dabf4431a..15a9af3719 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -187,6 +187,12 @@ def _parse(args: t.List[exp.Expression]) -> exp.Expression:
     return _parse
 
 
+def _build_to_date(args: t.List) -> exp.TsOrDsToDate:
+    expr = build_formatted_time(exp.TsOrDsToDate, "hive")(args)
+    expr.set("safe", True)
+    return expr
+
+
 class Hive(Dialect):
     ALIAS_POST_TABLESAMPLE = True
     IDENTIFIERS_CAN_START_WITH_DIGIT = True
@@ -318,7 +324,7 @@ class Parser(parser.Parser):
                 pair_delim=seq_get(args, 1) or exp.Literal.string(","),
                 key_value_delim=seq_get(args, 2) or exp.Literal.string(":"),
             ),
-            "TO_DATE": build_formatted_time(exp.TsOrDsToDate, "hive"),
+            "TO_DATE": _build_to_date,
             "TO_JSON": exp.JSONFormat.from_arg_list,
             "TRUNC": exp.TimestampTrunc.from_arg_list,
             "UNBASE64": exp.FromBase64.from_arg_list,


Test patch:
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index cc6e743147..3d9044cbb5 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -150,6 +150,8 @@ def test_to_date(self):
                 "hive": "TO_DATE(x)",
                 "presto": "CAST(CAST(x AS TIMESTAMP) AS DATE)",
                 "spark": "TO_DATE(x)",
+                "snowflake": "TRY_TO_DATE(x, 'yyyy-mm-DD')",
+                "databricks": "TO_DATE(x)",
             },
         )
         self.validate_all(
@@ -159,6 +161,8 @@ def test_to_date(self):
                 "hive": "TO_DATE(x, 'yyyy')",
                 "presto": "CAST(DATE_PARSE(x, '%Y') AS DATE)",
                 "spark": "TO_DATE(x, 'yyyy')",
+                "snowflake": "TRY_TO_DATE(x, 'yyyy')",
+                "databricks": "TO_DATE(x, 'yyyy')",
             },
         )
 
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index 60049c103e..5f3a97d0a5 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -55,7 +55,6 @@ def test_eq(self):
             parse_one("ROW() OVER(Partition by y)"),
             parse_one("ROW() OVER (partition BY y)"),
         )
-        self.assertEqual(parse_one("TO_DATE(x)", read="hive"), parse_one("ts_or_ds_to_date(x)"))
         self.assertEqual(exp.Table(pivots=[]), exp.Table())
         self.assertNotEqual(exp.Table(pivots=[None]), exp.Table())
         self.assertEqual(