Timestamp to date convert issue in Snowflake-CodePudding

I have one Snowflake table which contains TIMESTAMP_NTZ(9) column named as COLUMN_B_TS, it value is always like "2012-04-25 00:00:00.000", we want to run statement like below:

CREATE OR REPLACE TABLE "WH_A"."SCHEMA_A"."NEW_TABLE" 
SELECT COLUMN_A, to_DATE(COLUMN_B_TS), COLUMN_C 
FROM "WH_A"."SCHEMA_A"."EXISTING_TABLE"

but it returns error:

"SQL compilation error: Missing column specification"

I think there is no syntax error of above statement, so what is the problem here? Or Is there some restriction of the timestamp to date convert in Snowflake?

If I want to achieve above goal, how can I do? Thanks, keen to know the answer, block here now. Thanks!.

CodePudding user response：

You are looking for the

The second column has a name that is a column expression TO_DATE(...) and it has not a true alias/name.

The CREATE TABLE AS SELECT behaves very similar to CREATE VIEW AS SELECT on that matter:

If any of the columns in the view are based on expressions (not just simple column names), then you must supply a column name for each column in the view.

It is not possible to:

CREATE VIEW WH_A.SCHEMA_A.NEW_VIEW
AS
SELECT COLUMN_A, to_DATE(COLUMN_B_TS), COLUMN_C 
FROM WH_A.SCHEMA_A.EXISTSING_TABLE;
-- Missing column specification

There are two options to solve the missing specification:

a) provide an alias at query level

CREATE VIEW WH_A.SCHEMA_A.NEW_TABLE
AS
SELECT COLUMN_A, to_DATE(COLUMN_B_TS) AS COLUMN_B_TS, COLUMN_C 
FROM WH_A.SCHEMA_A.EXISTSING_TABLE;

b) provide explicit column specification(query stays the same)

CREATE TABLE WH_A.SCHEMA_A.NEW_TABLE(COLUMN_A TEXT, COLUMN_B_TS DATE, COLUMN_C INT)
AS
SELECT COLUMN_A, to_DATE(COLUMN_B_TS), COLUMN_C 
FROM WH_A.SCHEMA_A.EXISTSING_TABLE;