Repository: tobymao/sqlglot

Issue:
DuckDB STRFTIME arguments incorrectly ordered 
**Fully reproducible code snippet**

```python
import sqlglot
print(sqlglot.transpile("""

select strftime('%Y-%m-%d', current_timestamp)


""", read="sqlite", write="duckdb", pretty=True)[0])
```

produces 

```
SELECT
  STRFTIME('%Y-%m-%d', CURRENT_TIMESTAMP)
```

but that fails on DuckDB with 

```
duckdb> SELECT
   ...>   STRFTIME('%Y-%m-%d', CURRENT_TIMESTAMP)
   ...> ;
Binder Error: No function matches the given name and argument types 'strftime(STRING_LITERAL, TIMESTAMP WITH TIME ZONE)'. You might need to add explicit type casts.
        Candidate functions:
        strftime(DATE, VARCHAR) -> VARCHAR
        strftime(TIMESTAMP, VARCHAR) -> VARCHAR
        strftime(VARCHAR, DATE) -> VARCHAR
        strftime(VARCHAR, TIMESTAMP) -> VARCHAR

LINE 2:   STRFTIME('%Y-%m-%d', CURRENT_TIMESTAMP)
          ^

duckdb> 
```


**Official Documentation**

- DuckDB stftime https://duckdb.org/docs/sql/functions/dateformat.html 


Discussion:

Patch:
diff --git a/sqlglot/dialects/sqlite.py b/sqlglot/dialects/sqlite.py
index 63540a8e..ef7d9aa1 100644
--- a/sqlglot/dialects/sqlite.py
+++ b/sqlglot/dialects/sqlite.py
@@ -33,6 +33,14 @@ def _json_extract_sql(self: SQLite.Generator, expression: exp.JSONExtract) -> st
     return arrow_json_extract_sql(self, expression)
 
 
+def _build_strftime(args: t.List) -> exp.Anonymous | exp.TimeToStr:
+    if len(args) == 1:
+        args.append(exp.CurrentTimestamp())
+    if len(args) == 2:
+        return exp.TimeToStr(this=exp.TsOrDsToTimestamp(this=args[1]), format=args[0])
+    return exp.Anonymous(this="STRFTIME", expressions=args)
+
+
 def _transform_create(expression: exp.Expression) -> exp.Expression:
     """Move primary key to a column and enforce auto_increment on primary keys."""
     schema = expression.this
@@ -82,6 +90,7 @@ class SQLite(Dialect):
         FUNCTIONS = {
             **parser.Parser.FUNCTIONS,
             "EDITDIST3": exp.Levenshtein.from_arg_list,
+            "STRFTIME": _build_strftime,
         }
         STRING_ALIASES = True
 
@@ -152,7 +161,9 @@ class SQLite(Dialect):
             ),
             exp.TableSample: no_tablesample_sql,
             exp.TimeStrToTime: lambda self, e: self.sql(e, "this"),
+            exp.TimeToStr: lambda self, e: self.func("STRFTIME", e.args.get("format"), e.this),
             exp.TryCast: no_trycast_sql,
+            exp.TsOrDsToTimestamp: lambda self, e: self.sql(e, "this"),
         }
 
         # SQLite doesn't generally support CREATE TABLE .. properties
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 742dfcea..75751444 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5687,6 +5687,10 @@ class TsOrDsToTime(Func):
     pass
 
 
+class TsOrDsToTimestamp(Func):
+    pass
+
+
 class TsOrDiToDi(Func):
     pass
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index caec78d8..a67c7003 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -3516,6 +3516,13 @@ class Generator(metaclass=_Generator):
 
         return self.sql(exp.cast(this, "time"))
 
+    def tsordstotimestamp_sql(self, expression: exp.TsOrDsToTimestamp) -> str:
+        this = expression.this
+        if isinstance(this, exp.TsOrDsToTimestamp) or this.is_type(exp.DataType.Type.TIMESTAMP):
+            return self.sql(this)
+
+        return self.sql(exp.cast(this, "timestamp"))
+
     def tsordstodate_sql(self, expression: exp.TsOrDsToDate) -> str:
         this = expression.this
         time_format = self.format_time(expression)


Test patch:
diff --git a/tests/dialects/test_sqlite.py b/tests/dialects/test_sqlite.py
index e935c194..f3cde0b6 100644
--- a/tests/dialects/test_sqlite.py
+++ b/tests/dialects/test_sqlite.py
@@ -6,62 +6,6 @@ from sqlglot.helper import logger as helper_logger
 class TestSQLite(Validator):
     dialect = "sqlite"
 
-    def test_ddl(self):
-        for conflict_action in ("ABORT", "FAIL", "IGNORE", "REPLACE", "ROLLBACK"):
-            with self.subTest(f"ON CONFLICT {conflict_action}"):
-                self.validate_identity("CREATE TABLE a (b, c, UNIQUE (b, c) ON CONFLICT IGNORE)")
-
-        self.validate_identity("INSERT OR ABORT INTO foo (x, y) VALUES (1, 2)")
-        self.validate_identity("INSERT OR FAIL INTO foo (x, y) VALUES (1, 2)")
-        self.validate_identity("INSERT OR IGNORE INTO foo (x, y) VALUES (1, 2)")
-        self.validate_identity("INSERT OR REPLACE INTO foo (x, y) VALUES (1, 2)")
-        self.validate_identity("INSERT OR ROLLBACK INTO foo (x, y) VALUES (1, 2)")
-        self.validate_identity("CREATE TABLE foo (id INTEGER PRIMARY KEY ASC)")
-        self.validate_identity("CREATE TEMPORARY TABLE foo (id INTEGER)")
-
-        self.validate_all(
-            """
-            CREATE TABLE "Track"
-            (
-                CONSTRAINT "PK_Track" FOREIGN KEY ("TrackId"),
-                FOREIGN KEY ("AlbumId") REFERENCES "Album" (
-                    "AlbumId"
-                ) ON DELETE NO ACTION ON UPDATE NO ACTION,
-                FOREIGN KEY ("AlbumId") ON DELETE CASCADE ON UPDATE RESTRICT,
-                FOREIGN KEY ("AlbumId") ON DELETE SET NULL ON UPDATE SET DEFAULT
-            )
-            """,
-            write={
-                "sqlite": """CREATE TABLE "Track" (
-  CONSTRAINT "PK_Track" FOREIGN KEY ("TrackId"),
-  FOREIGN KEY ("AlbumId") REFERENCES "Album" (
-    "AlbumId"
-  ) ON DELETE NO ACTION ON UPDATE NO ACTION,
-  FOREIGN KEY ("AlbumId") ON DELETE CASCADE ON UPDATE RESTRICT,
-  FOREIGN KEY ("AlbumId") ON DELETE SET NULL ON UPDATE SET DEFAULT
-)""",
-            },
-            pretty=True,
-        )
-        self.validate_all(
-            "CREATE TABLE z (a INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT)",
-            read={
-                "mysql": "CREATE TABLE z (a INT UNIQUE PRIMARY KEY AUTO_INCREMENT)",
-            },
-            write={
-                "sqlite": "CREATE TABLE z (a INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT)",
-                "mysql": "CREATE TABLE z (a INT UNIQUE PRIMARY KEY AUTO_INCREMENT)",
-                "postgres": "CREATE TABLE z (a INT GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE PRIMARY KEY)",
-            },
-        )
-        self.validate_all(
-            """CREATE TABLE "x" ("Name" NVARCHAR(200) NOT NULL)""",
-            write={
-                "sqlite": """CREATE TABLE "x" ("Name" TEXT(200) NOT NULL)""",
-                "mysql": "CREATE TABLE `x` (`Name` VARCHAR(200) NOT NULL)",
-            },
-        )
-
     def test_sqlite(self):
         self.validate_identity("SELECT DATE()")
         self.validate_identity("SELECT DATE('now', 'start of month', '+1 month', '-1 day')")
@@ -69,7 +13,6 @@ class TestSQLite(Validator):
         self.validate_identity("SELECT DATETIME(1092941466, 'auto')")
         self.validate_identity("SELECT DATETIME(1092941466, 'unixepoch', 'localtime')")
         self.validate_identity("SELECT UNIXEPOCH()")
-        self.validate_identity("SELECT STRFTIME('%s')")
         self.validate_identity("SELECT JULIANDAY('now') - JULIANDAY('1776-07-04')")
         self.validate_identity("SELECT UNIXEPOCH() - UNIXEPOCH('2004-01-01 02:34:56')")
         self.validate_identity("SELECT DATE('now', 'start of year', '+9 months', 'weekday 2')")
@@ -149,6 +92,29 @@ class TestSQLite(Validator):
             write={"snowflake": "LEAST(x, y, z)"},
         )
 
+    def test_strftime(self):
+        self.validate_identity("SELECT STRFTIME('%Y/%m/%d', 'now')")
+        self.validate_identity("SELECT STRFTIME('%Y-%m-%d', '2016-10-16', 'start of month')")
+        self.validate_identity(
+            "SELECT STRFTIME('%s')",
+            "SELECT STRFTIME('%s', CURRENT_TIMESTAMP)",
+        )
+
+        self.validate_all(
+            "SELECT STRFTIME('%Y-%m-%d', '2020-01-01 12:05:03')",
+            write={
+                "duckdb": "SELECT STRFTIME(CAST('2020-01-01 12:05:03' AS TIMESTAMP), '%Y-%m-%d')",
+                "sqlite": "SELECT STRFTIME('%Y-%m-%d', '2020-01-01 12:05:03')",
+            },
+        )
+        self.validate_all(
+            "SELECT STRFTIME('%Y-%m-%d', CURRENT_TIMESTAMP)",
+            write={
+                "duckdb": "SELECT STRFTIME(CAST(CURRENT_TIMESTAMP AS TIMESTAMP), '%Y-%m-%d')",
+                "sqlite": "SELECT STRFTIME('%Y-%m-%d', CURRENT_TIMESTAMP)",
+            },
+        )
+
     def test_datediff(self):
         self.validate_all(
             "DATEDIFF(a, b, 'day')",
@@ -194,3 +160,59 @@ class TestSQLite(Validator):
             )
 
             self.assertIn("Named columns are not supported in table alias.", cm.output[0])
+
+    def test_ddl(self):
+        for conflict_action in ("ABORT", "FAIL", "IGNORE", "REPLACE", "ROLLBACK"):
+            with self.subTest(f"ON CONFLICT {conflict_action}"):
+                self.validate_identity("CREATE TABLE a (b, c, UNIQUE (b, c) ON CONFLICT IGNORE)")
+
+        self.validate_identity("INSERT OR ABORT INTO foo (x, y) VALUES (1, 2)")
+        self.validate_identity("INSERT OR FAIL INTO foo (x, y) VALUES (1, 2)")
+        self.validate_identity("INSERT OR IGNORE INTO foo (x, y) VALUES (1, 2)")
+        self.validate_identity("INSERT OR REPLACE INTO foo (x, y) VALUES (1, 2)")
+        self.validate_identity("INSERT OR ROLLBACK INTO foo (x, y) VALUES (1, 2)")
+        self.validate_identity("CREATE TABLE foo (id INTEGER PRIMARY KEY ASC)")
+        self.validate_identity("CREATE TEMPORARY TABLE foo (id INTEGER)")
+
+        self.validate_all(
+            """
+            CREATE TABLE "Track"
+            (
+                CONSTRAINT "PK_Track" FOREIGN KEY ("TrackId"),
+                FOREIGN KEY ("AlbumId") REFERENCES "Album" (
+                    "AlbumId"
+                ) ON DELETE NO ACTION ON UPDATE NO ACTION,
+                FOREIGN KEY ("AlbumId") ON DELETE CASCADE ON UPDATE RESTRICT,
+                FOREIGN KEY ("AlbumId") ON DELETE SET NULL ON UPDATE SET DEFAULT
+            )
+            """,
+            write={
+                "sqlite": """CREATE TABLE "Track" (
+  CONSTRAINT "PK_Track" FOREIGN KEY ("TrackId"),
+  FOREIGN KEY ("AlbumId") REFERENCES "Album" (
+    "AlbumId"
+  ) ON DELETE NO ACTION ON UPDATE NO ACTION,
+  FOREIGN KEY ("AlbumId") ON DELETE CASCADE ON UPDATE RESTRICT,
+  FOREIGN KEY ("AlbumId") ON DELETE SET NULL ON UPDATE SET DEFAULT
+)""",
+            },
+            pretty=True,
+        )
+        self.validate_all(
+            "CREATE TABLE z (a INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT)",
+            read={
+                "mysql": "CREATE TABLE z (a INT UNIQUE PRIMARY KEY AUTO_INCREMENT)",
+            },
+            write={
+                "sqlite": "CREATE TABLE z (a INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT)",
+                "mysql": "CREATE TABLE z (a INT UNIQUE PRIMARY KEY AUTO_INCREMENT)",
+                "postgres": "CREATE TABLE z (a INT GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE PRIMARY KEY)",
+            },
+        )
+        self.validate_all(
+            """CREATE TABLE "x" ("Name" NVARCHAR(200) NOT NULL)""",
+            write={
+                "sqlite": """CREATE TABLE "x" ("Name" TEXT(200) NOT NULL)""",
+                "mysql": "CREATE TABLE `x` (`Name` VARCHAR(200) NOT NULL)",
+            },
+        )