title: SQL compilation error: Expression type does not match column data type, expecting TIMESTAMP_NTZ(9) but got TIMESTAMP_LTZ(9) for column CREATE_DT
body: <p>THE QUESTION: WHAT IS THE PROPER DDL TO USE FOR CREATE TABLE...DEFAULT...CURRENT_TIMESTAMP()</p>
  
  <p>I have a CREATE or REPLACE statement that uses a DEFAULT for the CREATE_DT column- when someone inserts data into the table, the current server date/time should populate the column</p>
  
  <pre><code>CREATE OR REPLACE TABLE "EDW_ADMIN"."ETL_SPROC_LOG" (   
     "ETL_SPROC_LOG_ID" NUMBER IDENTITY NOT NULL, 
     "OBJECT_NAME" VARCHAR2(250 CHAR) NOT NULL, 
     "LOG_ENTRY" VARCHAR2(1000 CHAR) NOT NULL, 
     "DYNAMIC_SQL" VARCHAR2(10000 CHAR) NULL,
     "DURATION" NUMBER NULL,
     "ROWS_AFFECTED" NUMBER NULL,
     "ERROR_CODE" VARCHAR2(200 CHAR) NULL, 
     "ERROR_DESC" VARCHAR2(4000 CHAR) NULL, 
     "CREATE_DT" TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
     "CREATE_USER" VARCHAR2(50) NOT NULL DEFAULT CURRENT_USER()
  );
  </code></pre>
  
  <p>When we retrieve data from the column, we would use the following to change the system date/time to our timezone.</p>
  
  <pre><code>ALTER SESSION SET TIMEZONE = 'AMERICA/NEW_YORK';
  </code></pre>
  
  <p>When we execute a sample insert statement like this one we get an error:</p>
  
  <pre><code>   INSERT INTO edw_admin.ETL_SPROC_LOG (OBJECT_NAME, LOG_ENTRY) VALUES ('OBJ', 'ENTRY1');
  
  SQL compilation error: Expression type does not match column data type, expecting TIMESTAMP_NTZ(9) but got TIMESTAMP_LTZ(9) for column CREATE_DT
  </code></pre>
  
  <p>What is the proper DDL to use for defining the DEFAULT CURRENT_TIMESTAMP()?  Do we need to alter the session in the DDL Script prior to the create statement setting the Time Zone to NTZ(9)?  I would think Snowflake has multiple servers in different timezones and therefore the System Time would be dependent on where the server is.</p>
  
  <p>The Snowflake Documentation says </p>
  
  <blockquote>
    <p>Returns the current timestamp for the system.
    <a href="https://docs.snowflake.net/manuals/sql-reference/functions/current_timestamp.html" rel="nofollow noreferrer">https://docs.snowflake.net/manuals/sql-reference/functions/current_timestamp.html</a></p>
  </blockquote>
  
  <p>It has no arguments to control the Timezone that it returns.  </p>
  
  <p>This page in the Snowflake Documentation alludes to the use of CONVERT_TIMEZONE( source_tz, target_tz, source_timestamp_ntz), but again if we are different timezones depending on the server the DEFAULT is executing on I'd think this would fail as well.</p>
  
  <p><a href="https://docs.snowflake.net/manuals/sql-reference/functions/convert_timezone.html" rel="nofollow noreferrer">https://docs.snowflake.net/manuals/sql-reference/functions/convert_timezone.html</a></p>