Repository: tobymao/sqlglot

Issue:
DuckDB Timestamp -> Spark Should Be TimestampNTZ
```
exp.DataType.build("TIMESTAMP", dialect="duckdb").sql(dialect="spark")
```

Results in: `TIMESTAMP`

Timestamp in DuckDB lacks time zone while in Spark it defaults to having a timezone. Therefore this conversion adds timezone unexpectedly.

DuckDB docs: https://duckdb.org/docs/stable/sql/data_types/timestamp
Spark docs: https://spark.apache.org/docs/3.5.3/sql-ref-datatypes.html

Discussion:

Solution patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 102e1889..03374f71 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -1040,6 +1040,7 @@ class BigQuery(Dialect):
             exp.DataType.Type.SMALLINT: "INT64",
             exp.DataType.Type.TEXT: "STRING",
             exp.DataType.Type.TIMESTAMP: "DATETIME",
+            exp.DataType.Type.TIMESTAMPNTZ: "DATETIME",
             exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
             exp.DataType.Type.TIMESTAMPLTZ: "TIMESTAMP",
             exp.DataType.Type.TINYINT: "INT64",
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 5191a1f3..585f87c0 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -1016,6 +1016,7 @@ class ClickHouse(Dialect):
             exp.DataType.Type.DECIMAL128: "Decimal128",
             exp.DataType.Type.DECIMAL256: "Decimal256",
             exp.DataType.Type.TIMESTAMP: "DateTime",
+            exp.DataType.Type.TIMESTAMPNTZ: "DateTime",
             exp.DataType.Type.TIMESTAMPTZ: "DateTime",
             exp.DataType.Type.DOUBLE: "Float64",
             exp.DataType.Type.ENUM: "Enum",
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index c1550f42..73bd0ed2 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -321,6 +321,7 @@ class DuckDB(Dialect):
             "BPCHAR": TokenType.TEXT,
             "CHAR": TokenType.TEXT,
             "CHARACTER VARYING": TokenType.TEXT,
+            "DATETIME": TokenType.TIMESTAMPNTZ,
             "DETACH": TokenType.DETACH,
             "EXCLUDE": TokenType.EXCEPT,
             "LOGICAL": TokenType.BOOLEAN,
@@ -330,6 +331,7 @@ class DuckDB(Dialect):
             "SIGNED": TokenType.INT,
             "STRING": TokenType.TEXT,
             "SUMMARIZE": TokenType.SUMMARIZE,
+            "TIMESTAMP": TokenType.TIMESTAMPNTZ,
             "TIMESTAMP_S": TokenType.TIMESTAMP_S,
             "TIMESTAMP_MS": TokenType.TIMESTAMP_MS,
             "TIMESTAMP_NS": TokenType.TIMESTAMP_NS,
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index a67f03bf..e085fa70 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -503,6 +503,7 @@ class Hive(Dialect):
             exp.DataType.Type.ROWVERSION: "BINARY",
             exp.DataType.Type.TEXT: "STRING",
             exp.DataType.Type.TIME: "TIMESTAMP",
+            exp.DataType.Type.TIMESTAMPNTZ: "TIMESTAMP",
             exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
             exp.DataType.Type.UTINYINT: "SMALLINT",
             exp.DataType.Type.VARBINARY: "BINARY",
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 927642ab..208038d6 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -818,6 +818,7 @@ class MySQL(Dialect):
             exp.DataType.Type.DATETIME2: "DATETIME",
             exp.DataType.Type.SMALLDATETIME: "DATETIME",
             exp.DataType.Type.TIMESTAMP: "DATETIME",
+            exp.DataType.Type.TIMESTAMPNTZ: "DATETIME",
             exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
             exp.DataType.Type.TIMESTAMPLTZ: "TIMESTAMP",
         }
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 3013bf25..185907b2 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -266,6 +266,7 @@ class Oracle(Dialect):
             exp.DataType.Type.NCHAR: "NCHAR",
             exp.DataType.Type.TEXT: "CLOB",
             exp.DataType.Type.TIMETZ: "TIME",
+            exp.DataType.Type.TIMESTAMPNTZ: "TIMESTAMP",
             exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
             exp.DataType.Type.BINARY: "BLOB",
             exp.DataType.Type.VARBINARY: "BLOB",
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 61a6dba4..181ca110 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -539,6 +539,7 @@ class Postgres(Dialect):
             exp.DataType.Type.VARBINARY: "BYTEA",
             exp.DataType.Type.ROWVERSION: "BYTEA",
             exp.DataType.Type.DATETIME: "TIMESTAMP",
+            exp.DataType.Type.TIMESTAMPNTZ: "TIMESTAMP",
             exp.DataType.Type.BLOB: "BYTEA",
         }
 
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index e9cfa2bb..934ade06 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -910,6 +910,7 @@ class TSQL(Dialect):
         COPY_PARAMS_EQ_REQUIRED = True
         PARSE_JSON_NAME = None
         EXCEPT_INTERSECT_SUPPORT_ALL_CLAUSE = False
+        ALTER_SET_TYPE = ""
 
         EXPRESSIONS_WITHOUT_NESTED_CTES = {
             exp.Create,
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index cccb92ae..5b46a11c 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -3346,7 +3346,8 @@ class Generator(metaclass=_Generator):
             collate = f" COLLATE {collate}" if collate else ""
             using = self.sql(expression, "using")
             using = f" USING {using}" if using else ""
-            return f"ALTER COLUMN {this} {self.ALTER_SET_TYPE} {dtype}{collate}{using}"
+            alter_set_type = self.ALTER_SET_TYPE + " " if self.ALTER_SET_TYPE else ""
+            return f"ALTER COLUMN {this} {alter_set_type}{dtype}{collate}{using}"
 
         default = self.sql(expression, "default")
         if default:


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index d7b141de..3353404d 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -428,6 +428,7 @@ class TestDuckDB(Validator):
             "SELECT STRFTIME(CAST('2020-01-01' AS TIMESTAMP), CONCAT('%Y', '%m'))",
             write={
                 "duckdb": "SELECT STRFTIME(CAST('2020-01-01' AS TIMESTAMP), CONCAT('%Y', '%m'))",
+                "spark": "SELECT DATE_FORMAT(CAST('2020-01-01' AS TIMESTAMP_NTZ), CONCAT(COALESCE('yyyy', ''), COALESCE('MM', '')))",
                 "tsql": "SELECT FORMAT(CAST('2020-01-01' AS DATETIME2), CONCAT('yyyy', 'MM'))",
             },
         )
@@ -1110,6 +1111,28 @@ class TestDuckDB(Validator):
             },
         )
 
+        self.validate_all(
+            "SELECT TIMESTAMP 'foo'",
+            write={
+                "duckdb": "SELECT CAST('foo' AS TIMESTAMP)",
+                "hive": "SELECT CAST('foo' AS TIMESTAMP)",
+                "spark2": "SELECT CAST('foo' AS TIMESTAMP)",
+                "spark": "SELECT CAST('foo' AS TIMESTAMP_NTZ)",
+                "postgres": "SELECT CAST('foo' AS TIMESTAMP)",
+                "mysql": "SELECT CAST('foo' AS DATETIME)",
+                "clickhouse": "SELECT CAST('foo' AS Nullable(DateTime))",
+                "databricks": "SELECT CAST('foo' AS TIMESTAMP_NTZ)",
+                "snowflake": "SELECT CAST('foo' AS TIMESTAMPNTZ)",
+                "redshift": "SELECT CAST('foo' AS TIMESTAMP)",
+                "tsql": "SELECT CAST('foo' AS DATETIME2)",
+                "presto": "SELECT CAST('foo' AS TIMESTAMP)",
+                "trino": "SELECT CAST('foo' AS TIMESTAMP)",
+                "oracle": "SELECT CAST('foo' AS TIMESTAMP)",
+                "bigquery": "SELECT CAST('foo' AS DATETIME)",
+                "starrocks": "SELECT CAST('foo' AS DATETIME)",
+            },
+        )
+
     def test_sample(self):
         self.validate_identity(
             "SELECT * FROM tbl USING SAMPLE 5",
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 173cd792..f2bbee5f 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -1311,7 +1311,7 @@ COMMENT='客户账户表'"""
 
     def test_timestamp_trunc(self):
         hive_dialects = ("spark", "databricks")
-        for dialect in ("postgres", "snowflake", "duckdb", *hive_dialects):
+        for dialect in ("postgres", "snowflake", *hive_dialects):
             for unit in (
                 "SECOND",
                 "DAY",
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index e2eda73a..99591f00 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -1062,6 +1062,16 @@ WHERE
                 "tsql": "ALTER TABLE a ADD b INTEGER, c INTEGER",
             },
         )
+        self.validate_all(
+            "ALTER TABLE a ALTER COLUMN b INTEGER",
+            read={
+                "": "ALTER TABLE a ALTER COLUMN b INT",
+            },
+            write={
+                "": "ALTER TABLE a ALTER COLUMN b SET DATA TYPE INT",
+                "tsql": "ALTER TABLE a ALTER COLUMN b INTEGER",
+            },
+        )
         self.validate_all(
             "CREATE TABLE #mytemp (a INTEGER, b CHAR(2), c TIME(4), d FLOAT(24))",
             write={