Timestamp to date convert issue in Snowflake

You are looking for the Create Table As Select (CTAS) pattern, and thus need to include the AS, but given the error message:

Missing column specification

that implies you have that in your real code, but the second column needs a valid name.

create table EXISTING_TABLE (column_a int, column_b timestamp, column_c text);

insert into EXISTING_TABLE values
    (1, '2022-08-28 16:54:01'::timestamp, 'row 1'),
    (2, '2022-08-28 16:54:02'::timestamp, 'row 2');

CREATE OR REPLACE TABLE NEW_TABLE AS
    SELECT COLUMN_A, to_DATE(COLUMN_B) as column_b, COLUMN_C 
    FROM EXISTING_TABLE;

select * from NEW_TABLE;

I think there is no syntax error of above statement, so what is the problem here?

The SELECT statement is fine as long as it is run standalone.

CREATE DATABASE WH_A;
CREATE SCHEMA SCHEMA_A;
CREATE TABLE EXISTSING_TABLE(COLUMN_A TEXT, COLUMN_B_TS TIMESTAMP_NTZ(9), 
                             COLUMN_C INT);
INSERT INTO EXISTSING_TABLE VALUES ('A', CURRENT_TIMESTAMP(), 1);

SELECT COLUMN_A, to_DATE(COLUMN_B_TS), COLUMN_C 
FROM WH_A.SCHEMA_A.EXISTSING_TABLE;

Output:

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;

When we create a table using select statement (CTAS), we have to give name to every columns. In your case Snowflake does not know how to name the column containing to_DATE(COLUMN_B_TS). Giving it a name will resolve the problem.

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