Repository: tobymao/sqlglot

Issue:
`to_date` with formatting is not supported by `spark` dialects
Spark supports [to_date(e: Column, fmt: String):](https://sparkbyexamples.com/spark/spark-sql-date-and-time-functions/#to_date) function, but trying to parse it produces
> sqlglot.errors.ParseError: The number of provided arguments (2) is greater than the maximum number of supported arguments (1)

Steps to reproduce:

```python
>>> import sqlglot
>>> sqlglot.transpile(sqlglot.transpile("""SELECT TO_DATE('2022-1-1', 'yyyy-dd-mm')""", read='mysql', write='spark')[0], read='spark')
```

Discussion:

Patch:
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 0da85f37..d12adc0f 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -227,12 +227,21 @@ def struct_extract_sql(self, expression):
     return f"{this}.{struct_key}"
 
 
-def format_time_lambda(exp_class, dialect, default=False):
+def format_time_lambda(exp_class, dialect, default=None):
+    """Helper used for time expressions.
+
+    Args
+        exp_class (Class): the expression class to instantiate
+        dialect (string): sql dialect
+        default (Option[bool | str]): the default format, True being time
+    """
+
     def _format_time(args):
         return exp_class(
             this=list_get(args, 0),
             format=Dialect[dialect].format_time(
-                list_get(args, 1) or (Dialect[dialect].time_format if default else None)
+                list_get(args, 1)
+                or (Dialect[dialect].time_format if default is True else default)
             ),
         )
 
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index b0e1c7f6..1d5b87a6 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -19,6 +19,10 @@ def _unix_to_time(self, expression):
     return f"TO_TIMESTAMP(CAST({self.sql(expression, 'this')} AS BIGINT))"
 
 
+def _str_to_time_sql(self, expression):
+    return f"STRPTIME({self.sql(expression, 'this')}, {self.format_time(expression)})"
+
+
 def _ts_or_ds_add(self, expression):
     this = self.sql(expression, "this")
     e = self.sql(expression, "expression")
@@ -26,6 +30,13 @@ def _ts_or_ds_add(self, expression):
     return f"STRFTIME(CAST({this} AS DATE) + INTERVAL {e} {unit}, {DuckDB.date_format})"
 
 
+def _ts_or_ds_to_date_sql(self, expression):
+    time_format = self.format_time(expression)
+    if time_format and time_format not in (DuckDB.time_format, DuckDB.date_format):
+        return f"CAST({_str_to_time_sql(self, expression)} AS DATE)"
+    return f"CAST({self.sql(expression, 'this')} AS DATE)"
+
+
 def _date_add(self, expression):
     this = self.sql(expression, "this")
     e = self.sql(expression, "expression")
@@ -96,7 +107,8 @@ class DuckDB(Dialect):
             exp.RegexpSplit: rename_func("STR_SPLIT_REGEX"),
             exp.SafeDivide: no_safe_divide_sql,
             exp.Split: rename_func("STR_SPLIT"),
-            exp.StrToTime: lambda self, e: f"STRPTIME({self.sql(e, 'this')}, {self.format_time(e)})",
+            exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)",
+            exp.StrToTime: _str_to_time_sql,
             exp.StrToUnix: lambda self, e: f"EPOCH(STRPTIME({self.sql(e, 'this')}, {self.format_time(e)}))",
             exp.Struct: _struct_pack_sql,
             exp.TableSample: no_tablesample_sql,
@@ -108,8 +120,7 @@ class DuckDB(Dialect):
             exp.TimeToUnix: rename_func("EPOCH"),
             exp.TsOrDiToDi: lambda self, e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS STRING), '-', ''), 1, 8) AS INT)",
             exp.TsOrDsAdd: _ts_or_ds_add,
-            exp.TsOrDsToDateStr: lambda self, e: f"STRFTIME(CAST({self.sql(e, 'this')} AS DATE), {DuckDB.date_format})",
-            exp.TsOrDsToDate: lambda self, e: f"CAST({self.sql(e, 'this')} AS DATE)",
+            exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
             exp.UnixToStr: lambda self, e: f"STRFTIME({_unix_to_time(self, e)}, {self.format_time(e)})",
             exp.UnixToTime: _unix_to_time,
             exp.UnixToTimeStr: lambda self, e: f"STRFTIME({_unix_to_time(self, e)}, {DuckDB.time_format})",
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index b9edc183..9650a4dd 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -61,11 +61,20 @@ def _str_to_unix(self, expression):
     return f"UNIX_TIMESTAMP({csv(self.sql(expression, 'this'), _time_format(self, expression))})"
 
 
+def _str_to_date(self, expression):
+    this = self.sql(expression, "this")
+    time_format = self.format_time(expression)
+    if time_format == Hive.date_format:
+        return f"CAST({this} AS DATE)"
+    return f"CAST(DATE_FORMAT({this}, {time_format}) AS DATE)"
+
+
 def _str_to_time(self, expression):
-    time_format = self.sql(expression, "format")
+    this = self.sql(expression, "this")
+    time_format = self.format_time(expression)
     if time_format in (Hive.time_format, Hive.date_format):
-        return f"DATE_FORMAT({self.sql(expression, 'this')}, {Hive.time_format})"
-    return f"FROM_UNIXTIME({_str_to_unix(self, expression)})"
+        return f"CAST({this} AS TIMESTAMP)"
+    return f"CAST(DATE_FORMAT({this}, {time_format}) AS TIMESTAMP)"
 
 
 def _time_format(self, expression):
@@ -197,7 +206,7 @@ class Hive(Dialect):
             "COLLECT_SET": exp.SetAgg.from_arg_list,
             "SIZE": exp.ArraySize.from_arg_list,
             "SPLIT": exp.RegexpSplit.from_arg_list,
-            "TO_DATE": exp.TsOrDsToDateStr.from_arg_list,
+            "TO_DATE": format_time_lambda(exp.TsOrDsToDate, "hive"),
             "UNIX_TIMESTAMP": format_time_lambda(exp.StrToUnix, "hive", True),
             "YEAR": lambda args: exp.Year(this=exp.TsOrDsToDate.from_arg_list(args)),
         }
@@ -249,6 +258,7 @@ class Hive(Dialect):
             exp.SetAgg: rename_func("COLLECT_SET"),
             exp.Split: lambda self, e: f"SPLIT({self.sql(e, 'this')}, CONCAT('\\\\Q', {self.sql(e, 'expression')}))",
             exp.StrPosition: lambda self, e: f"LOCATE({csv(self.sql(e, 'substr'), self.sql(e, 'this'), self.sql(e, 'position'))})",
+            exp.StrToDate: _str_to_date,
             exp.StrToTime: _str_to_time,
             exp.StrToUnix: _str_to_unix,
             exp.StructExtract: struct_extract_sql,
@@ -261,7 +271,6 @@ class Hive(Dialect):
             exp.TimeToUnix: rename_func("UNIX_TIMESTAMP"),
             exp.TsOrDiToDi: lambda self, e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS STRING), '-', ''), 1, 8) AS INT)",
             exp.TsOrDsAdd: lambda self, e: f"DATE_ADD({self.sql(e, 'this')}, {self.sql(e, 'expression')})",
-            exp.TsOrDsToDateStr: rename_func("TO_DATE"),
             exp.TsOrDsToDate: rename_func("TO_DATE"),
             exp.TryCast: no_trycast_sql,
             exp.UnixToStr: lambda self, e: f"FROM_UNIXTIME({csv(self.sql(e, 'this'), _time_format(self, e))})",
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index 444050e4..46cee77d 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -97,14 +97,17 @@ def _str_position_sql(self, expression):
     return f"STRPOS({this}, {substr})"
 
 
-def _ts_or_ds_to_date_str_sql(self, expression):
-    this = self.sql(expression, "this")
-    return f"DATE_FORMAT(DATE_PARSE(SUBSTR(CAST({this} AS VARCHAR), 1, 10), {Presto.date_format}), {Presto.date_format})"
+def _str_to_time_sql(self, expression):
+    return f"DATE_PARSE({self.sql(expression, 'this')}, {self.format_time(expression)})"
 
 
 def _ts_or_ds_to_date_sql(self, expression):
-    this = self.sql(expression, "this")
-    return f"CAST(DATE_PARSE(SUBSTR(CAST({this} AS VARCHAR), 1, 10), {Presto.date_format}) AS DATE)"
+    time_format = self.format_time(expression)
+    if time_format and time_format not in (Presto.time_format, Presto.date_format):
+        return f"CAST({_str_to_time_sql(self, expression)} AS DATE)"
+    return (
+        f"CAST(SUBSTR(CAST({self.sql(expression, 'this')} AS VARCHAR), 1, 10) AS DATE)"
+    )
 
 
 def _ts_or_ds_add_sql(self, expression):
@@ -203,7 +206,8 @@ class Presto(Dialect):
             exp.Schema: _schema_sql,
             exp.SortArray: _no_sort_array,
             exp.StrPosition: _str_position_sql,
-            exp.StrToTime: lambda self, e: f"DATE_PARSE({self.sql(e, 'this')}, {self.format_time(e)})",
+            exp.StrToDate: lambda self, e: f"CAST({_str_to_time_sql(self, e)} AS DATE)",
+            exp.StrToTime: _str_to_time_sql,
             exp.StrToUnix: lambda self, e: f"TO_UNIXTIME(DATE_PARSE({self.sql(e, 'this')}, {self.format_time(e)}))",
             exp.StructExtract: struct_extract_sql,
             exp.TableFormatProperty: lambda self, e: f"TABLE_FORMAT = '{e.text('value').upper()}'",
@@ -215,7 +219,6 @@ class Presto(Dialect):
             exp.TimeToUnix: rename_func("TO_UNIXTIME"),
             exp.TsOrDiToDi: lambda self, e: f"CAST(SUBSTR(REPLACE(CAST({self.sql(e, 'this')} AS VARCHAR), '-', ''), 1, 8) AS INT)",
             exp.TsOrDsAdd: _ts_or_ds_add_sql,
-            exp.TsOrDsToDateStr: _ts_or_ds_to_date_str_sql,
             exp.TsOrDsToDate: _ts_or_ds_to_date_sql,
             exp.UnixToStr: lambda self, e: f"DATE_FORMAT(FROM_UNIXTIME({self.sql(e, 'this')}), {self.format_time(e)})",
             exp.UnixToTime: rename_func("FROM_UNIXTIME"),
diff --git a/sqlglot/dialects/spark.py b/sqlglot/dialects/spark.py
index 5c83fcd1..87fd3dda 100644
--- a/sqlglot/dialects/spark.py
+++ b/sqlglot/dialects/spark.py
@@ -19,6 +19,14 @@ def _map_sql(self, expression):
     return f"MAP_FROM_ARRAYS({keys}, {values})"
 
 
+def _str_to_date(self, expression):
+    this = self.sql(expression, "this")
+    time_format = self.format_time(expression)
+    if time_format == Hive.date_format:
+        return f"TO_DATE({this})"
+    return f"TO_DATE({this}, {time_format})"
+
+
 class Spark(Hive):
     class Parser(Hive.Parser):
         FUNCTIONS = {
@@ -68,6 +76,7 @@ class Spark(Hive):
             exp.BitwiseLeftShift: rename_func("SHIFTLEFT"),
             exp.BitwiseRightShift: rename_func("SHIFTRIGHT"),
             exp.Hint: lambda self, e: f" /*+ {self.expressions(e).strip()} */",
+            exp.StrToDate: _str_to_date,
             exp.StrToTime: lambda self, e: f"TO_TIMESTAMP({self.sql(e, 'this')}, {self.format_time(e)})",
             exp.Create: _create_sql,
             exp.Map: _map_sql,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 1f724f41..ba46cd28 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2178,6 +2178,10 @@ class StrPosition(Func):
     arg_types = {"this": True, "substr": True, "position": False}
 
 
+class StrToDate(Func):
+    arg_types = {"this": True, "format": True}
+
+
 class StrToTime(Func):
     arg_types = {"this": True, "format": True}
 
@@ -2248,7 +2252,7 @@ class TsOrDsToDateStr(Func):
 
 
 class TsOrDsToDate(Func):
-    pass
+    arg_types = {"this": True, "format": False}
 
 
 class TsOrDiToDi(Func):
@@ -2292,10 +2296,18 @@ class Year(Func):
 
 
 def _norm_args(expression):
-    return {
-        k: [_norm_arg(a) for a in arg] if isinstance(arg, list) else _norm_arg(arg)
-        for k, arg in expression.args.items()
-    }
+    args = {}
+
+    for k, arg in expression.args.items():
+        if isinstance(arg, list):
+            arg = [_norm_arg(a) for a in arg]
+        else:
+            arg = _norm_arg(arg)
+
+        if arg is not None:
+            args[k] = arg
+
+    return args
 
 
 def _norm_arg(arg):
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 3ca11c76..6772add5 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -106,7 +106,15 @@ class Parser:
     """
 
     FUNCTIONS = {
-        name: f.from_arg_list for f in exp.ALL_FUNCTIONS for name in f.sql_names()
+        **{name: f.from_arg_list for f in exp.ALL_FUNCTIONS for name in f.sql_names()},
+        "TS_OR_DS_TO_DATE_STR": lambda args: exp.Substring(
+            this=exp.Cast(
+                this=list_get(args, 0),
+                to=exp.DataType(this=exp.DataType.Type.TEXT),
+            ),
+            start=exp.Literal.number(1),
+            length=exp.Literal.number(10),
+        ),
     }
 
     NO_PAREN_FUNCTIONS = {


Test patch:
diff --git a/tests/test_dialects.py b/tests/test_dialects.py
index c6b0ff36..bf46a64b 100644
--- a/tests/test_dialects.py
+++ b/tests/test_dialects.py
@@ -166,7 +166,7 @@ class TestDialects(unittest.TestCase):
         )
         self.validate(
             "TS_OR_DS_TO_DATE_STR(x)",
-            "STRFTIME(CAST(x AS DATE), '%Y-%m-%d')",
+            "SUBSTRING(CAST(x AS TEXT), 1, 10)",
             identity=False,
             write="duckdb",
         )
@@ -801,13 +801,13 @@ class TestDialects(unittest.TestCase):
         )
         self.validate(
             "DATE_PARSE(x, '%Y-%m-%d %H:%i:%s')",
-            "FROM_UNIXTIME(UNIX_TIMESTAMP(x))",
+            "CAST(x AS TIMESTAMP)",
             read="presto",
             write="hive",
         )
         self.validate(
             "DATE_PARSE(x, '%Y-%m-%d')",
-            "FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'yyyy-MM-dd'))",
+            "CAST(x AS TIMESTAMP)",
             read="presto",
             write="hive",
         )
@@ -876,7 +876,7 @@ class TestDialects(unittest.TestCase):
         )
         self.validate(
             "TS_OR_DS_TO_DATE(x)",
-            "CAST(DATE_PARSE(SUBSTR(CAST(x AS VARCHAR), 1, 10), '%Y-%m-%d') AS DATE)",
+            "CAST(SUBSTR(CAST(x AS VARCHAR), 1, 10) AS DATE)",
             write="presto",
             identity=False,
         )
@@ -964,7 +964,7 @@ class TestDialects(unittest.TestCase):
         self.validate("MONTH(x)", "MONTH(x)", read="presto", write="hive")
         self.validate(
             "MONTH(x)",
-            "MONTH(CAST(DATE_PARSE(SUBSTR(CAST(x AS VARCHAR), 1, 10), '%Y-%m-%d') AS DATE))",
+            "MONTH(CAST(SUBSTR(CAST(x AS VARCHAR), 1, 10) AS DATE))",
             read="hive",
             write="presto",
         )
@@ -972,7 +972,7 @@ class TestDialects(unittest.TestCase):
         self.validate("DAY(x)", "DAY(x)", read="presto", write="hive")
         self.validate(
             "DAY(x)",
-            "DAY(CAST(DATE_PARSE(SUBSTR(CAST(x AS VARCHAR), 1, 10), '%Y-%m-%d') AS DATE))",
+            "DAY(CAST(SUBSTR(CAST(x AS VARCHAR), 1, 10) AS DATE))",
             read="hive",
             write="presto",
         )
@@ -981,7 +981,7 @@ class TestDialects(unittest.TestCase):
         self.validate("YEAR(x)", "YEAR(x)", read="presto", write="hive")
         self.validate(
             "YEAR(x)",
-            "YEAR(CAST(DATE_PARSE(SUBSTR(CAST(x AS VARCHAR), 1, 10), '%Y-%m-%d') AS DATE))",
+            "YEAR(CAST(SUBSTR(CAST(x AS VARCHAR), 1, 10) AS DATE))",
             read="hive",
             write="presto",
         )
@@ -1373,22 +1373,46 @@ class TestDialects(unittest.TestCase):
         )
         self.validate(
             "STR_TO_TIME('2020-01-01', 'yyyy-MM-dd')",
-            "DATE_FORMAT('2020-01-01', 'yyyy-MM-dd HH:mm:ss')",
+            "CAST('2020-01-01' AS TIMESTAMP)",
             write="hive",
             identity=False,
         )
         self.validate(
             "STR_TO_TIME('2020-01-01', 'yyyy-MM-dd HH:mm:ss')",
-            "DATE_FORMAT('2020-01-01', 'yyyy-MM-dd HH:mm:ss')",
+            "CAST('2020-01-01' AS TIMESTAMP)",
             write="hive",
             identity=False,
         )
         self.validate(
             "STR_TO_TIME(x, 'yyyy')",
-            "FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'yyyy'))",
+            "CAST(DATE_FORMAT(x, 'yyyy') AS TIMESTAMP)",
+            write="hive",
+            identity=False,
+        )
+        self.validate(
+            "STR_TO_DATE(x, 'yyyy')",
+            "CAST(DATE_FORMAT(x, 'yyyy') AS DATE)",
             write="hive",
             identity=False,
         )
+        self.validate(
+            "STR_TO_DATE(x, 'yyyy-MM-dd')",
+            "CAST(x AS DATE)",
+            write="hive",
+            identity=False,
+        )
+        self.validate(
+            "TS_OR_DS_TO_DATE_STR(x)",
+            "SUBSTRING(CAST(x AS STRING), 1, 10)",
+            identity=False,
+            write="hive",
+        )
+        self.validate(
+            "DATE_FORMAT('2020-01-01', 'yyyy-MM-dd HH:mm:ss')",
+            "DATE_FORMAT('2020-01-01', '%Y-%m-%d %H:%i:%S')",
+            read="hive",
+            write="presto",
+        )
         self.validate(
             "DATE_ADD('2020-01-01', 1)",
             "TS_OR_DS_ADD('2020-01-01', 1, 'DAY')",
@@ -1456,16 +1480,15 @@ class TestDialects(unittest.TestCase):
         )
         self.validate(
             "DATEDIFF(TO_DATE(y), x)",
-            "DATE_DIFF('day', CAST(DATE_PARSE(SUBSTR(CAST(x AS VARCHAR), 1, 10), '%Y-%m-%d') AS DATE), "
-            "CAST(DATE_PARSE(SUBSTR(CAST(DATE_FORMAT(DATE_PARSE(SUBSTR(CAST(y AS VARCHAR), 1, 10), '%Y-%m-%d'), '%Y-%m-%d') "
-            "AS VARCHAR), 1, 10), '%Y-%m-%d') AS DATE))",
+            "DATE_DIFF('day', CAST(SUBSTR(CAST(x AS VARCHAR), 1, 10) AS DATE), "
+            "CAST(SUBSTR(CAST(CAST(SUBSTR(CAST(y AS VARCHAR), 1, 10) AS DATE) AS VARCHAR), 1, 10) AS DATE))",
             read="hive",
             write="presto",
         )
         self.validate(
             "DATEDIFF('2020-01-02', '2020-01-01')",
-            "DATE_DIFF('day', CAST(DATE_PARSE(SUBSTR(CAST('2020-01-01' AS VARCHAR), 1, 10), '%Y-%m-%d') AS DATE), "
-            "CAST(DATE_PARSE(SUBSTR(CAST('2020-01-02' AS VARCHAR), 1, 10), '%Y-%m-%d') AS DATE))",
+            "DATE_DIFF('day', CAST(SUBSTR(CAST('2020-01-01' AS VARCHAR), 1, 10) AS DATE), "
+            "CAST(SUBSTR(CAST('2020-01-02' AS VARCHAR), 1, 10) AS DATE))",
             read="hive",
             write="presto",
         )
@@ -1521,7 +1544,7 @@ class TestDialects(unittest.TestCase):
         )
         self.validate(
             "TO_DATE(x)",
-            "TS_OR_DS_TO_DATE_STR(x)",
+            "TS_OR_DS_TO_DATE(x)",
             read="hive",
             identity=False,
         )
@@ -1925,6 +1948,25 @@ TBLPROPERTIES (
             write="spark",
         )
 
+        self.validate(
+            "TO_DATE(x, 'yyyy-MM-dd')",
+            "CAST(SUBSTR(CAST(x AS VARCHAR), 1, 10) AS DATE)",
+            read="spark",
+            write="presto",
+        )
+        self.validate(
+            "TO_DATE(x, 'yyyy')",
+            "CAST(DATE_PARSE(x, '%Y') AS DATE)",
+            read="spark",
+            write="presto",
+        )
+        self.validate(
+            "TO_DATE(x, 'yyyy')",
+            "CAST(STRPTIME(x, '%Y') AS DATE)",
+            read="spark",
+            write="duckdb",
+        )
+
     def test_snowflake(self):
         self.validate(
             'x:a:"b c"',
diff --git a/tests/test_expressions.py b/tests/test_expressions.py
index 823bacce..bdc71c82 100644
--- a/tests/test_expressions.py
+++ b/tests/test_expressions.py
@@ -27,7 +27,7 @@ class TestExpressions(unittest.TestCase):
             parse_one("ROW() OVER (partition BY y)"),
         )
         self.assertEqual(
-            parse_one("TO_DATE(x)", read="hive"), parse_one("ts_or_ds_to_date_str(x)")
+            parse_one("TO_DATE(x)", read="hive"), parse_one("ts_or_ds_to_date(x)")
         )
 
     def test_find(self):
@@ -336,7 +336,7 @@ class TestExpressions(unittest.TestCase):
         self.assertIsInstance(parse_one("TIME_STR_TO_UNIX(a)"), exp.TimeStrToUnix)
         self.assertIsInstance(parse_one("TS_OR_DS_ADD(a, 1, 'day')"), exp.TsOrDsAdd)
         self.assertIsInstance(parse_one("TS_OR_DS_TO_DATE(a)"), exp.TsOrDsToDate)
-        self.assertIsInstance(parse_one("TS_OR_DS_TO_DATE_STR(a)"), exp.TsOrDsToDateStr)
+        self.assertIsInstance(parse_one("TS_OR_DS_TO_DATE_STR(a)"), exp.Substring)
         self.assertIsInstance(parse_one("UNIX_TO_STR(a, 'format')"), exp.UnixToStr)
         self.assertIsInstance(parse_one("UNIX_TO_TIME(a)"), exp.UnixToTime)
         self.assertIsInstance(parse_one("UNIX_TO_TIME_STR(a)"), exp.UnixToTimeStr)
diff --git a/tests/test_transpile.py b/tests/test_transpile.py
index f847d8db..e7e833f5 100644
--- a/tests/test_transpile.py
+++ b/tests/test_transpile.py
@@ -173,16 +173,18 @@ class TestTranspile(unittest.TestCase):
         )
 
         self.validate(
-            "STR_TO_TIME(x, 'y')", "FROM_UNIXTIME(UNIX_TIMESTAMP(x, 'y'))", write="hive"
+            "STR_TO_TIME(x, 'y')",
+            "CAST(DATE_FORMAT(x, 'y') AS TIMESTAMP)",
+            write="hive",
         )
         self.validate(
             "STR_TO_TIME(x, 'yyyy-MM-dd HH:mm:ss')",
-            "DATE_FORMAT(x, 'yyyy-MM-dd HH:mm:ss')",
+            "CAST(x AS TIMESTAMP)",
             write="hive",
         )
         self.validate(
             "STR_TO_TIME(x, 'yyyy-MM-dd')",
-            "DATE_FORMAT(x, 'yyyy-MM-dd HH:mm:ss')",
+            "CAST(x AS TIMESTAMP)",
             write="hive",
         )