Repository: tobymao/sqlglot

Issue:
Translate dialect - MySQL to Snowflake
Hello,

I'm trying to translate MySQL dialect into Snowflake dialect.
I did some tests, but the translation doesn't seem to work.

> It can be used to format SQL or translate between [19 different dialects](https://github.com/tobymao/sqlglot/blob/main/sqlglot/dialects/__init__.py) like [DuckDB](https://duckdb.org/), [Presto](https://prestodb.io/), [Spark](https://spark.apache.org/), [Snowflake](https://www.snowflake.com/en/), and [BigQuery](https://cloud.google.com/bigquery/).

**Actual behaviour**
```python
sqlglot.transpile("select DATE_FORMAT(created_date, '%Y-%m')", read="mysql", write="snowflake")[0]
# "SELECT DATE_FORMAT(created_date, '%Y-%m')"
```

**Expected behaviour**
```python
sqlglot.transpile("select DATE_FORMAT(created_date, '%Y-%m')", read="mysql", write="snowflake")[0]
# "SELECT to_char(created_date, 'yyyy-mm')"
```

`sqlglot==11.5.2`

Thanks! 

Discussion:
GeorgeSittas: I studied `format_time_lambda` a bit and I realized my implementation could be greatly simplified. I almost replicated what we do for Drill, with the addition that I added the cast to `TIMESTAMP` for Snowflake (we need this otherwise `TO_CHAR` breaks if the first argument is not a date/time/timestamp value, i.e. a simple string).

Also added and fixed several tests. I've manually tested these changes across all affected dialects, they looked good (hopefully didn't missed something).

Patch:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index d64efbfe..8f5ae4e8 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -4,6 +4,7 @@ from sqlglot import exp, generator, parser, tokens
 from sqlglot.dialects.dialect import (
     Dialect,
     arrow_json_extract_scalar_sql,
+    format_time_lambda,
     locate_to_strposition,
     max_or_greatest,
     min_or_least,
@@ -115,6 +116,7 @@ class MySQL(Dialect):
         "%k": "%-H",
         "%l": "%-I",
         "%T": "%H:%M:%S",
+        "%W": "%a",
     }
 
     class Tokenizer(tokens.Tokenizer):
@@ -187,13 +189,14 @@ class MySQL(Dialect):
         FUNCTIONS = {
             **parser.Parser.FUNCTIONS,  # type: ignore
             "DATE_ADD": _date_add(exp.DateAdd),
+            "DATE_FORMAT": format_time_lambda(exp.TimeToStr, "mysql"),
             "DATE_SUB": _date_add(exp.DateSub),
-            "STR_TO_DATE": _str_to_date,
-            "LOCATE": locate_to_strposition,
             "INSTR": lambda args: exp.StrPosition(substr=seq_get(args, 1), this=seq_get(args, 0)),
             "LEFT": lambda args: exp.Substring(
                 this=seq_get(args, 0), start=exp.Literal.number(1), length=seq_get(args, 1)
             ),
+            "LOCATE": locate_to_strposition,
+            "STR_TO_DATE": _str_to_date,
         }
 
         FUNCTION_PARSERS = {
@@ -393,27 +396,28 @@ class MySQL(Dialect):
             **generator.Generator.TRANSFORMS,  # type: ignore
             exp.CurrentDate: no_paren_current_date_sql,
             exp.CurrentTimestamp: lambda *_: "CURRENT_TIMESTAMP",
-            exp.ILike: no_ilike_sql,
-            exp.JSONExtractScalar: arrow_json_extract_scalar_sql,
-            exp.Max: max_or_greatest,
-            exp.Min: min_or_least,
-            exp.TableSample: no_tablesample_sql,
-            exp.TryCast: no_trycast_sql,
+            exp.DateDiff: lambda self, e: self.func("DATEDIFF", e.this, e.expression),
             exp.DateAdd: _date_add_sql("ADD"),
-            exp.DateDiff: lambda self, e: f"DATEDIFF({self.format_args(e.this, e.expression)})",
             exp.DateSub: _date_add_sql("SUB"),
             exp.DateTrunc: _date_trunc_sql,
-            exp.DayOfWeek: rename_func("DAYOFWEEK"),
             exp.DayOfMonth: rename_func("DAYOFMONTH"),
+            exp.DayOfWeek: rename_func("DAYOFWEEK"),
             exp.DayOfYear: rename_func("DAYOFYEAR"),
-            exp.WeekOfYear: rename_func("WEEKOFYEAR"),
             exp.GroupConcat: lambda self, e: f"""GROUP_CONCAT({self.sql(e, "this")} SEPARATOR {self.sql(e, "separator") or "','"})""",
-            exp.StrToDate: _str_to_date_sql,
-            exp.StrToTime: _str_to_date_sql,
-            exp.Trim: _trim_sql,
+            exp.ILike: no_ilike_sql,
+            exp.JSONExtractScalar: arrow_json_extract_scalar_sql,
+            exp.Max: max_or_greatest,
+            exp.Min: min_or_least,
             exp.NullSafeEQ: lambda self, e: self.binary(e, "<=>"),
             exp.NullSafeNEQ: lambda self, e: self.not_sql(self.binary(e, "<=>")),
             exp.StrPosition: strposition_to_locate_sql,
+            exp.StrToDate: _str_to_date_sql,
+            exp.StrToTime: _str_to_date_sql,
+            exp.TableSample: no_tablesample_sql,
+            exp.TimeToStr: lambda self, e: self.func("DATE_FORMAT", e.this, self.format_time(e)),
+            exp.Trim: _trim_sql,
+            exp.TryCast: no_trycast_sql,
+            exp.WeekOfYear: rename_func("WEEKOFYEAR"),
         }
 
         TYPE_MAPPING = generator.Generator.TYPE_MAPPING.copy()
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 34bc3bda..fa7c477b 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -23,14 +23,14 @@ from sqlglot.parser import binary_range_parser
 from sqlglot.tokens import TokenType
 
 
-def _check_int(s):
+def _check_int(s: str) -> bool:
     if s[0] in ("-", "+"):
         return s[1:].isdigit()
     return s.isdigit()
 
 
 # from https://docs.snowflake.com/en/sql-reference/functions/to_timestamp.html
-def _snowflake_to_timestamp(args):
+def _snowflake_to_timestamp(args: t.Sequence) -> t.Union[exp.StrToTime, exp.UnixToTime]:
     if len(args) == 2:
         first_arg, second_arg = args
         if second_arg.is_string:
@@ -69,7 +69,7 @@ def _snowflake_to_timestamp(args):
     return exp.UnixToTime.from_arg_list(args)
 
 
-def _unix_to_time_sql(self, expression):
+def _unix_to_time_sql(self: generator.Generator, expression: exp.UnixToTime) -> str:
     scale = expression.args.get("scale")
     timestamp = self.sql(expression, "this")
     if scale in [None, exp.UnixToTime.SECONDS]:
@@ -84,8 +84,12 @@ def _unix_to_time_sql(self, expression):
 
 # https://docs.snowflake.com/en/sql-reference/functions/date_part.html
 # https://docs.snowflake.com/en/sql-reference/functions-date-time.html#label-supported-date-time-parts
-def _parse_date_part(self):
+def _parse_date_part(self: parser.Parser) -> t.Optional[exp.Expression]:
     this = self._parse_var() or self._parse_type()
+
+    if not this:
+        return None
+
     self._match(TokenType.COMMA)
     expression = self._parse_bitwise()
 
@@ -112,7 +116,7 @@ def _parse_date_part(self):
 
 
 # https://docs.snowflake.com/en/sql-reference/functions/div0
-def _div0_to_if(args):
+def _div0_to_if(args: t.Sequence) -> exp.Expression:
     cond = exp.EQ(this=seq_get(args, 1), expression=exp.Literal.number(0))
     true = exp.Literal.number(0)
     false = exp.Div(this=seq_get(args, 0), expression=seq_get(args, 1))
@@ -120,18 +124,18 @@ def _div0_to_if(args):
 
 
 # https://docs.snowflake.com/en/sql-reference/functions/zeroifnull
-def _zeroifnull_to_if(args):
+def _zeroifnull_to_if(args: t.Sequence) -> exp.Expression:
     cond = exp.Is(this=seq_get(args, 0), expression=exp.Null())
     return exp.If(this=cond, true=exp.Literal.number(0), false=seq_get(args, 0))
 
 
 # https://docs.snowflake.com/en/sql-reference/functions/zeroifnull
-def _nullifzero_to_if(args):
+def _nullifzero_to_if(args: t.Sequence) -> exp.Expression:
     cond = exp.EQ(this=seq_get(args, 0), expression=exp.Literal.number(0))
     return exp.If(this=cond, true=exp.Null(), false=seq_get(args, 0))
 
 
-def _datatype_sql(self, expression):
+def _datatype_sql(self: generator.Generator, expression: exp.DataType) -> str:
     if expression.this == exp.DataType.Type.ARRAY:
         return "ARRAY"
     elif expression.this == exp.DataType.Type.MAP:
@@ -155,9 +159,8 @@ class Snowflake(Dialect):
         "MM": "%m",
         "mm": "%m",
         "DD": "%d",
-        "dd": "%d",
-        "d": "%-d",
-        "DY": "%w",
+        "dd": "%-d",
+        "DY": "%a",
         "dy": "%w",
         "HH24": "%H",
         "hh24": "%H",
@@ -287,26 +290,29 @@ class Snowflake(Dialect):
             ),
             exp.DateStrToDate: datestrtodate_sql,
             exp.DataType: _datatype_sql,
+            exp.DayOfWeek: rename_func("DAYOFWEEK"),
             exp.If: rename_func("IFF"),
-            exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"),
-            exp.LogicalOr: rename_func("BOOLOR_AGG"),
             exp.LogicalAnd: rename_func("BOOLAND_AGG"),
-            exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"),
+            exp.LogicalOr: rename_func("BOOLOR_AGG"),
+            exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"),
+            exp.Max: max_or_greatest,
+            exp.Min: min_or_least,
             exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
             exp.StrPosition: lambda self, e: self.func(
                 "POSITION", e.args.get("substr"), e.this, e.args.get("position")
             ),
             exp.StrToTime: lambda self, e: f"TO_TIMESTAMP({self.sql(e, 'this')}, {self.format_time(e)})",
-            exp.TimestampTrunc: timestamptrunc_sql,
             exp.TimeStrToTime: timestrtotime_sql,
             exp.TimeToUnix: lambda self, e: f"EXTRACT(epoch_second FROM {self.sql(e, 'this')})",
-            exp.Trim: lambda self, e: self.func("TRIM", e.this, e.expression),
+            exp.TimeToStr: lambda self, e: self.func(
+                "TO_CHAR", exp.cast(e.this, "timestamp"), self.format_time(e)
+            ),
+            exp.TimestampTrunc: timestamptrunc_sql,
             exp.ToChar: lambda self, e: self.function_fallback_sql(e),
+            exp.Trim: lambda self, e: self.func("TRIM", e.this, e.expression),
             exp.TsOrDsToDate: ts_or_ds_to_date_sql("snowflake"),
             exp.UnixToTime: _unix_to_time_sql,
-            exp.DayOfWeek: rename_func("DAYOFWEEK"),
-            exp.Max: max_or_greatest,
-            exp.Min: min_or_least,
+            exp.VarMap: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"),
         }
 
         TYPE_MAPPING = {
@@ -324,12 +330,12 @@ class Snowflake(Dialect):
             exp.SetProperty: exp.Properties.Location.UNSUPPORTED,
         }
 
-        def except_op(self, expression):
+        def except_op(self, expression: exp.Except) -> str:
             if not expression.args.get("distinct", False):
                 self.unsupported("EXCEPT with All is not supported in Snowflake")
             return super().except_op(expression)
 
-        def intersect_op(self, expression):
+        def intersect_op(self, expression: exp.Intersect) -> str:
             if not expression.args.get("distinct", False):
                 self.unsupported("INTERSECT with All is not supported in Snowflake")
             return super().intersect_op(expression)


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index f6187284..c5ed3292 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -239,6 +239,71 @@ class TestMySQL(Validator):
             write={"mysql": "MATCH(a.b) AGAINST('abc')"},
         )
 
+    def test_date_format(self):
+        self.validate_all(
+            "SELECT DATE_FORMAT('2017-06-15', '%Y')",
+            write={
+                "mysql": "SELECT DATE_FORMAT('2017-06-15', '%Y')",
+                "snowflake": "SELECT TO_CHAR(CAST('2017-06-15' AS TIMESTAMPNTZ), 'yyyy')",
+            },
+        )
+        self.validate_all(
+            "SELECT DATE_FORMAT('2017-06-15', '%m')",
+            write={
+                "mysql": "SELECT DATE_FORMAT('2017-06-15', '%m')",
+                "snowflake": "SELECT TO_CHAR(CAST('2017-06-15' AS TIMESTAMPNTZ), 'mm')",
+            },
+        )
+        self.validate_all(
+            "SELECT DATE_FORMAT('2017-06-15', '%d')",
+            write={
+                "mysql": "SELECT DATE_FORMAT('2017-06-15', '%d')",
+                "snowflake": "SELECT TO_CHAR(CAST('2017-06-15' AS TIMESTAMPNTZ), 'DD')",
+            },
+        )
+        self.validate_all(
+            "SELECT DATE_FORMAT('2017-06-15', '%Y-%m-%d')",
+            write={
+                "mysql": "SELECT DATE_FORMAT('2017-06-15', '%Y-%m-%d')",
+                "snowflake": "SELECT TO_CHAR(CAST('2017-06-15' AS TIMESTAMPNTZ), 'yyyy-mm-DD')",
+            },
+        )
+        self.validate_all(
+            "SELECT DATE_FORMAT('2017-06-15 22:23:34', '%H')",
+            write={
+                "mysql": "SELECT DATE_FORMAT('2017-06-15 22:23:34', '%H')",
+                "snowflake": "SELECT TO_CHAR(CAST('2017-06-15 22:23:34' AS TIMESTAMPNTZ), 'hh24')",
+            },
+        )
+        self.validate_all(
+            "SELECT DATE_FORMAT('2017-06-15', '%w')",
+            write={
+                "mysql": "SELECT DATE_FORMAT('2017-06-15', '%w')",
+                "snowflake": "SELECT TO_CHAR(CAST('2017-06-15' AS TIMESTAMPNTZ), 'dy')",
+            },
+        )
+        self.validate_all(
+            "SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y')",
+            write={
+                "mysql": "SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y')",
+                "snowflake": "SELECT TO_CHAR(CAST('2009-10-04 22:23:00' AS TIMESTAMPNTZ), 'DY mmmm yyyy')",
+            },
+        )
+        self.validate_all(
+            "SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s')",
+            write={
+                "mysql": "SELECT DATE_FORMAT('2007-10-04 22:23:00', '%T')",
+                "snowflake": "SELECT TO_CHAR(CAST('2007-10-04 22:23:00' AS TIMESTAMPNTZ), 'hh24:mi:ss')",
+            },
+        )
+        self.validate_all(
+            "SELECT DATE_FORMAT('1900-10-04 22:23:00', '%d %y %a %d %m %b')",
+            write={
+                "mysql": "SELECT DATE_FORMAT('1900-10-04 22:23:00', '%d %y %W %d %m %b')",
+                "snowflake": "SELECT TO_CHAR(CAST('1900-10-04 22:23:00' AS TIMESTAMPNTZ), 'DD yy DY DD mm mon')",
+            },
+        )
+
     def test_mysql(self):
         self.validate_all(
             "SELECT a FROM tbl FOR UPDATE",
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index eb423a58..9607ec42 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -255,19 +255,18 @@ class TestSnowflake(Validator):
             write={
                 "bigquery": "SELECT PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2013-04-05 01:02:03')",
                 "snowflake": "SELECT TO_TIMESTAMP('2013-04-05 01:02:03', 'yyyy-mm-dd hh24:mi:ss')",
-                "spark": "SELECT TO_TIMESTAMP('2013-04-05 01:02:03', 'yyyy-MM-dd HH:mm:ss')",
+                "spark": "SELECT TO_TIMESTAMP('2013-04-05 01:02:03', 'yyyy-MM-d HH:mm:ss')",
             },
         )
         self.validate_all(
-            "SELECT TO_TIMESTAMP('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss')",
+            "SELECT TO_TIMESTAMP('04/05/2013 01:02:03', 'mm/DD/yyyy hh24:mi:ss')",
             read={
                 "bigquery": "SELECT PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', '04/05/2013 01:02:03')",
                 "duckdb": "SELECT STRPTIME('04/05/2013 01:02:03', '%m/%d/%Y %H:%M:%S')",
-                "snowflake": "SELECT TO_TIMESTAMP('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss')",
             },
             write={
                 "bigquery": "SELECT PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', '04/05/2013 01:02:03')",
-                "snowflake": "SELECT TO_TIMESTAMP('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss')",
+                "snowflake": "SELECT TO_TIMESTAMP('04/05/2013 01:02:03', 'mm/DD/yyyy hh24:mi:ss')",
                 "spark": "SELECT TO_TIMESTAMP('04/05/2013 01:02:03', 'MM/dd/yyyy HH:mm:ss')",
             },
         )
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index d9ee4aea..7e309e49 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -399,7 +399,7 @@ WHERE
         self.validate_all(
             "SELECT CONVERT(VARCHAR(10), testdb.dbo.test.x, 120) y FROM testdb.dbo.test",
             write={
-                "mysql": "SELECT CAST(TIME_TO_STR(testdb.dbo.test.x, '%Y-%m-%d %H:%M:%S') AS VARCHAR(10)) AS y FROM testdb.dbo.test",
+                "mysql": "SELECT CAST(DATE_FORMAT(testdb.dbo.test.x, '%Y-%m-%d %T') AS VARCHAR(10)) AS y FROM testdb.dbo.test",
                 "spark": "SELECT CAST(DATE_FORMAT(testdb.dbo.test.x, 'yyyy-MM-dd HH:mm:ss') AS VARCHAR(10)) AS y FROM testdb.dbo.test",
             },
         )