SYSDATE Function - Oracle to Snowflake Migration

In Oracle, SYSDATE function returns the current date and time including seconds but without the fraction:

Oracle:

  -- Set default DATE format to show the time part
  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 
  -- Get the current date and time
  SELECT SYSDATE FROM dual; 
  # 2022-04-21 22:58:13

In Snowflake, you can use CURRENT_TIMESTAMP(0) function to get the same result. Note that if you use the CURRENT_DATE function it does not include the time part.

Snowflake:

  -- Get the current date and time without fraction
  SELECT CURRENT_TIMESTAMP(0) FROM dual; 
  # 2022-04-21 22:58:13.000 +0000
 
  -- Get the current date without time part
  SELECT CURRENT_DATE FROM dual; 
  # 2022-04-21

For more information, see Oracle to Snowflake Migration.