Repository: tobymao/sqlglot

Issue:
transpile snowflake try_to_date to duckdb
**Fully reproducible code snippet**
Please include a fully reproducible code snippet or the input sql, dialect, and expected output.

```python
from sqlglot import transpile

transpile("SELECT try_to_date(x, 'MMDDYYYY')", read="snowflake", write="duckdb")
```
output
```
SELECT TRY_TO_DATE(x, 'MMDDYYYY')
```
however, duckdb has no `try_to_date` function, most relevant I think is [strptime](https://duckdb.org/docs/sql/functions/dateformat.html)

**Official Documentation**

snowflake related function [try_to_date](https://docs.snowflake.com/en/sql-reference/functions/try_to_date)
duckdb related function [strptime](https://duckdb.org/docs/sql/functions/dateformat.html)

Discussion:

Patch:
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index a18cfbed..60221323 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -28,7 +28,9 @@ if t.TYPE_CHECKING:
 
 
 # from https://docs.snowflake.com/en/sql-reference/functions/to_timestamp.html
-def _build_timestamp(name: str, kind: exp.DataType.Type) -> t.Callable[[t.List], exp.Func]:
+def _build_datetime(
+    name: str, kind: exp.DataType.Type, safe: bool = False
+) -> t.Callable[[t.List], exp.Func]:
     def _builder(args: t.List) -> exp.Func:
         value = seq_get(args, 0)
 
@@ -46,7 +48,10 @@ def _build_timestamp(name: str, kind: exp.DataType.Type) -> t.Callable[[t.List],
                     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
         return exp.Anonymous(this=name, expressions=args)
 
     return _builder
@@ -336,6 +341,7 @@ class Snowflake(Dialect):
             "BIT_XOR": binary_from_function(exp.BitwiseXor),
             "BOOLXOR": binary_from_function(exp.Xor),
             "CONVERT_TIMEZONE": _build_convert_timezone,
+            "DATE": _build_datetime("DATE", exp.DataType.Type.DATE),
             "DATE_TRUNC": _date_trunc_to_time,
             "DATEADD": lambda args: exp.DateAdd(
                 this=seq_get(args, 2),
@@ -363,19 +369,19 @@ class Snowflake(Dialect):
             "TIMESTAMPDIFF": _build_datediff,
             "TIMESTAMPFROMPARTS": _build_timestamp_from_parts,
             "TIMESTAMP_FROM_PARTS": _build_timestamp_from_parts,
+            "TRY_TO_DATE": _build_datetime("TRY_TO_DATE", exp.DataType.Type.DATE, safe=True),
+            "TO_DATE": _build_datetime("TO_DATE", exp.DataType.Type.DATE),
             "TO_NUMBER": lambda args: exp.ToNumber(
                 this=seq_get(args, 0),
                 format=seq_get(args, 1),
                 precision=seq_get(args, 2),
                 scale=seq_get(args, 3),
             ),
-            "TO_TIME": _build_timestamp("TO_TIME", exp.DataType.Type.TIME),
-            "TO_TIMESTAMP": _build_timestamp("TO_TIMESTAMP", exp.DataType.Type.TIMESTAMP),
-            "TO_TIMESTAMP_LTZ": _build_timestamp(
-                "TO_TIMESTAMP_LTZ", exp.DataType.Type.TIMESTAMPLTZ
-            ),
-            "TO_TIMESTAMP_NTZ": _build_timestamp("TO_TIMESTAMP_NTZ", exp.DataType.Type.TIMESTAMP),
-            "TO_TIMESTAMP_TZ": _build_timestamp("TO_TIMESTAMP_TZ", exp.DataType.Type.TIMESTAMPTZ),
+            "TO_TIME": _build_datetime("TO_TIME", exp.DataType.Type.TIME),
+            "TO_TIMESTAMP": _build_datetime("TO_TIMESTAMP", exp.DataType.Type.TIMESTAMP),
+            "TO_TIMESTAMP_LTZ": _build_datetime("TO_TIMESTAMP_LTZ", exp.DataType.Type.TIMESTAMPLTZ),
+            "TO_TIMESTAMP_NTZ": _build_datetime("TO_TIMESTAMP_NTZ", exp.DataType.Type.TIMESTAMP),
+            "TO_TIMESTAMP_TZ": _build_datetime("TO_TIMESTAMP_TZ", exp.DataType.Type.TIMESTAMPTZ),
             "TO_VARCHAR": exp.ToChar.from_arg_list,
             "ZEROIFNULL": _build_if_from_zeroifnull,
         }
@@ -798,6 +804,9 @@ class Snowflake(Dialect):
             exp.Trim: lambda self, e: self.func("TRIM", e.this, e.expression),
             exp.TsOrDsAdd: date_delta_sql("DATEADD", cast=True),
             exp.TsOrDsDiff: date_delta_sql("DATEDIFF"),
+            exp.TsOrDsToDate: lambda self, e: self.func(
+                "TRY_TO_DATE" if e.args.get("safe") else "TO_DATE", e.this, self.format_time(e)
+            ),
             exp.UnixToTime: rename_func("TO_TIMESTAMP"),
             exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"),
             exp.WeekOfYear: rename_func("WEEKOFYEAR"),
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index b19dc852..7a8fef4e 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1931,6 +1931,7 @@ class Insert(DDL, DML):
     arg_types = {
         "hint": False,
         "with": False,
+        "is_function": False,
         "this": True,
         "expression": False,
         "conflict": False,
@@ -5663,7 +5664,7 @@ class TsOrDsToDateStr(Func):
 
 
 class TsOrDsToDate(Func):
-    arg_types = {"this": True, "format": False}
+    arg_types = {"this": True, "format": False, "safe": False}
 
 
 class TsOrDsToTime(Func):
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index a61b4b75..077e5ff0 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1512,7 +1512,9 @@ class Generator(metaclass=_Generator):
         alternative = expression.args.get("alternative")
         alternative = f" OR {alternative}" if alternative else ""
         ignore = " IGNORE" if expression.args.get("ignore") else ""
-
+        is_function = expression.args.get("is_function")
+        if is_function:
+            this = f"{this} FUNCTION"
         this = f"{this} {self.sql(expression, 'this')}"
 
         exists = " IF EXISTS" if expression.args.get("exists") else ""
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 60364141..0c7e9957 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -2170,6 +2170,7 @@ class Parser(metaclass=_Parser):
         ignore = self._match(TokenType.IGNORE)
         local = self._match_text_seq("LOCAL")
         alternative = None
+        is_function = None
 
         if self._match_text_seq("DIRECTORY"):
             this: t.Optional[exp.Expression] = self.expression(
@@ -2185,7 +2186,9 @@ class Parser(metaclass=_Parser):
             self._match(TokenType.INTO)
             comments += ensure_list(self._prev_comments)
             self._match(TokenType.TABLE)
-            this = self._parse_table(schema=True)
+            is_function = self._match(TokenType.FUNCTION)
+
+            this = self._parse_table(schema=True) if not is_function else self._parse_function()
 
         returning = self._parse_returning()
 
@@ -2193,6 +2196,7 @@ class Parser(metaclass=_Parser):
             exp.Insert,
             comments=comments,
             hint=hint,
+            is_function=is_function,
             this=this,
             by_name=self._match_text_seq("BY", "NAME"),
             exists=self._parse_exists(),


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index edf3da12..8a40899a 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -390,6 +390,17 @@ class TestClickhouse(Validator):
         )
         self.validate_identity("SYSTEM STOP MERGES foo.bar", check_command_warning=True)
 
+        self.validate_identity(
+            "INSERT INTO FUNCTION s3('url', 'CSV', 'name String, value UInt32', 'gzip') SELECT name, value FROM existing_table"
+        )
+        self.validate_identity(
+            "INSERT INTO FUNCTION remote('localhost', default.simple_table) VALUES (100, 'inserted via remote()')"
+        )
+        self.validate_identity(
+            """INSERT INTO TABLE FUNCTION hdfs('hdfs://hdfs1:9000/test', 'TSV', 'name String, column2 UInt32, column3 UInt32') VALUES ('test', 1, 2)""",
+            """INSERT INTO FUNCTION hdfs('hdfs://hdfs1:9000/test', 'TSV', 'name String, column2 UInt32, column3 UInt32') VALUES ('test', 1, 2)""",
+        )
+
     def test_cte(self):
         self.validate_identity("WITH 'x' AS foo SELECT foo")
         self.validate_identity("WITH ['c'] AS field_names SELECT field_names")
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index 2b3973c5..6e8b737d 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -700,7 +700,7 @@ class TestDialect(Validator):
                 "hive": "TO_DATE(x)",
                 "postgres": "CAST(x AS DATE)",
                 "presto": "CAST(CAST(x AS TIMESTAMP) AS DATE)",
-                "snowflake": "CAST(x AS DATE)",
+                "snowflake": "TO_DATE(x)",
                 "doris": "TO_DATE(x)",
                 "mysql": "DATE(x)",
             },
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 22c3fd53..19a1eb58 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -71,7 +71,6 @@ WHERE
         self.validate_identity("INITCAP('iqamqinterestedqinqthisqtopic', 'q')")
         self.validate_identity("CAST(x AS GEOMETRY)")
         self.validate_identity("OBJECT_CONSTRUCT(*)")
-        self.validate_identity("SELECT TO_DATE('2019-02-28') + INTERVAL '1 day, 1 year'")
         self.validate_identity("SELECT CAST('2021-01-01' AS DATE) + INTERVAL '1 DAY'")
         self.validate_identity("SELECT HLL(*)")
         self.validate_identity("SELECT HLL(a)")
@@ -979,6 +978,35 @@ WHERE
         self.validate_identity("DATE_PART(yyy, x)", "DATE_PART(YEAR, x)")
         self.validate_identity("DATE_TRUNC(yr, x)", "DATE_TRUNC('YEAR', x)")
 
+        self.validate_identity("TO_DATE('12345')").assert_is(exp.Anonymous)
+
+        self.validate_identity(
+            "SELECT TO_DATE('2019-02-28') + INTERVAL '1 day, 1 year'",
+            "SELECT CAST('2019-02-28' AS DATE) + INTERVAL '1 day, 1 year'",
+        )
+
+        self.validate_all(
+            "DATE('01-01-2000', 'MM-DD-YYYY')",
+            write={
+                "snowflake": "TO_DATE('01-01-2000', 'mm-DD-yyyy')",
+                "duckdb": "CAST(STRPTIME('01-01-2000', '%m-%d-%Y') AS DATE)",
+            },
+        )
+        self.validate_all(
+            "TO_DATE('01-01-2000', 'MM-DD-YYYY')",
+            write={
+                "snowflake": "TO_DATE('01-01-2000', 'mm-DD-yyyy')",
+                "duckdb": "CAST(STRPTIME('01-01-2000', '%m-%d-%Y') AS DATE)",
+            },
+        )
+        self.validate_all(
+            "TRY_TO_DATE('01-01-2000', 'MM-DD-YYYY')",
+            write={
+                "snowflake": "TRY_TO_DATE('01-01-2000', 'mm-DD-yyyy')",
+                "duckdb": "CAST(STRPTIME('01-01-2000', '%m-%d-%Y') AS DATE)",
+            },
+        )
+
     def test_semi_structured_types(self):
         self.validate_identity("SELECT CAST(a AS VARIANT)")
         self.validate_identity("SELECT CAST(a AS ARRAY)")
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index b79409fe..1ac13fba 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -367,7 +367,7 @@ TBLPROPERTIES (
                 "hive": "SELECT CAST(DATEDIFF(TO_DATE('2020-12-31'), TO_DATE('2020-01-01')) / 7 AS INT)",
                 "postgres": "SELECT CAST(EXTRACT(days FROM (CAST(CAST('2020-12-31' AS DATE) AS TIMESTAMP) - CAST(CAST('2020-01-01' AS DATE) AS TIMESTAMP))) / 7 AS BIGINT)",
                 "redshift": "SELECT DATEDIFF(WEEK, CAST('2020-01-01' AS DATE), CAST('2020-12-31' AS DATE))",
-                "snowflake": "SELECT DATEDIFF(WEEK, CAST('2020-01-01' AS DATE), CAST('2020-12-31' AS DATE))",
+                "snowflake": "SELECT DATEDIFF(WEEK, TO_DATE('2020-01-01'), TO_DATE('2020-12-31'))",
                 "spark": "SELECT DATEDIFF(WEEK, TO_DATE('2020-01-01'), TO_DATE('2020-12-31'))",
             },
         )
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index 1d2f03b6..4efd7b91 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -1571,7 +1571,7 @@ WHERE
                 "postgres": "CAST(DATE_TRUNC('MONTH', CAST(CURRENT_TIMESTAMP AS DATE)) + INTERVAL '1 MONTH' - INTERVAL '1 DAY' AS DATE)",
                 "presto": "LAST_DAY_OF_MONTH(CAST(CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS DATE))",
                 "redshift": "LAST_DAY(CAST(GETDATE() AS DATE))",
-                "snowflake": "LAST_DAY(CAST(CURRENT_TIMESTAMP() AS DATE))",
+                "snowflake": "LAST_DAY(TO_DATE(CURRENT_TIMESTAMP()))",
                 "spark": "LAST_DAY(TO_DATE(CURRENT_TIMESTAMP()))",
                 "tsql": "EOMONTH(CAST(GETDATE() AS DATE))",
             },
@@ -1586,7 +1586,7 @@ WHERE
                 "postgres": "CAST(DATE_TRUNC('MONTH', CAST(CURRENT_TIMESTAMP AS DATE) + INTERVAL '-1 MONTH') + INTERVAL '1 MONTH' - INTERVAL '1 DAY' AS DATE)",
                 "presto": "LAST_DAY_OF_MONTH(DATE_ADD('MONTH', CAST(-1 AS BIGINT), CAST(CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS DATE)))",
                 "redshift": "LAST_DAY(DATEADD(MONTH, -1, CAST(GETDATE() AS DATE)))",
-                "snowflake": "LAST_DAY(DATEADD(MONTH, -1, CAST(CURRENT_TIMESTAMP() AS DATE)))",
+                "snowflake": "LAST_DAY(DATEADD(MONTH, -1, TO_DATE(CURRENT_TIMESTAMP())))",
                 "spark": "LAST_DAY(ADD_MONTHS(TO_DATE(CURRENT_TIMESTAMP()), -1))",
                 "tsql": "EOMONTH(DATEADD(MONTH, -1, CAST(GETDATE() AS DATE)))",
             },