Repository: tobymao/sqlglot
Issue category: Feature Request

Issue:
feat(snowflake): Adding support for DATE, TO_DATE, TRY_TO_DATE functions
Fixes #3152 

Add support for the mentioned functions by:

- Converting all of them to `exp.TsOrDsToDate` expressions if the format is present _or_ to date casts otherwise
- Adding a new argument `safe` in `exp.TsOrDsToDate` to preserve the roundtrip of `TRY_TO_DATE`, which has a different error handling behavior compared to `TO_DATE / DATE` 

For the transpilation issue #3152, no change was needed in DuckDB as it already transpiles the aforementioned expression to `strptime` (note that it produces a `TIMESTAMP`, thus the cast to `DATE`):

```
>>> import sqlglot
>>> sqlglot.transpile("SELECT try_to_date('01-01-1999', 'MM-DD-YYYY')", read="snowflake", write="duckdb")
SELECT CAST(STRPTIME('01-01-1999', '%m-%d-%Y') AS DATE)
```

Docs
-----------

1. [Snowflake TRY_TO_DATE](https://docs.snowflake.com/en/sql-reference/functions/try_to_date)
2. [Snowflake DATE / TO_DATE](https://docs.snowflake.com/en/sql-reference/functions/to_date)
3. [DuckDB strptime](https://duckdb.org/docs/sql/functions/dateformat.html#strptime-examples)


Hints:
the command above works great. 
```
>>> import sqlglot
>>> sqlglot.transpile("SELECT try_to_date('01-01-1999', 'MM-DD-YYYY')", read="snowflake", write="duckdb")
SELECT CAST(STRPTIME('01-01-1999', '%m-%d-%Y') AS DATE)
```

but seems this command still not work, when the parameter is a column_name instead of a string
```
>>> sqlglot.transpile("SELECT try_to_date(x, 'MMDDYYYY')", read="snowflake", write="duckdb")
SELECT TRY_TO_DATE(x, 'MMDDYYYY')
```
@VaggelisD this is because we've reused the logic that we have for `TO_TIMESTAMP`. In that function you can basically have both a numeric expression and a string expression as the first argument, so we couldn't safely convert it to a `StrToTime` without having a literal. It seems though that for `TO_DATE` and `TRY_TO_DATE` there's only one variant with two arguments - the `<str>, <fmt>` one - so it should be safe to convert it accordingly. Wanna take a look?

Patch:
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 6022132354..0ffe4cf0a3 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -48,10 +48,12 @@ def _builder(args: t.List) -> exp.Func:
                     return exp.UnixToTime(this=value, scale=seq_get(args, 1))
                 if not is_float(value.this):
                     return build_formatted_time(exp.StrToTime, "snowflake")(args)
-            if kind == exp.DataType.Type.DATE and not int_value:
-                formatted_exp = build_formatted_time(exp.TsOrDsToDate, "snowflake")(args)
-                formatted_exp.set("safe", safe)
-                return formatted_exp
+
+        if len(args) == 2 and kind == exp.DataType.Type.DATE:
+            formatted_exp = build_formatted_time(exp.TsOrDsToDate, "snowflake")(args)
+            formatted_exp.set("safe", safe)
+            return formatted_exp
+
         return exp.Anonymous(this=name, expressions=args)
 
     return _builder


Test patch:
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 19a1eb58a9..00e6169ac0 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -985,6 +985,17 @@ def test_timestamps(self):
             "SELECT CAST('2019-02-28' AS DATE) + INTERVAL '1 day, 1 year'",
         )
 
+        self.validate_identity("DATE(x)").assert_is(exp.Anonymous)
+        self.validate_identity("TO_DATE(x)").assert_is(exp.Anonymous)
+        self.validate_identity("TRY_TO_DATE(x)").assert_is(exp.Anonymous)
+
+        self.validate_all(
+            "TO_DATE(x, 'MM-DD-YYYY')",
+            write={
+                "snowflake": "TO_DATE(x, 'mm-DD-yyyy')",
+                "duckdb": "CAST(STRPTIME(x, '%m-%d-%Y') AS DATE)",
+            },
+        )
         self.validate_all(
             "DATE('01-01-2000', 'MM-DD-YYYY')",
             write={