TO_DATE Function - Oracle to Snowflake Migration

In Oracle, the TO_DATE function converts a string to the DATE data type that includes both date and time part.

Although Snowflake also provides the TO_DATE function, its result is the DATE data type that contains only date (year, month and day), so you should to convert Oracle TO_DATE to Snowflake TO_TIMESTAMP.

Oracle:

  -- Set default DATE format to show the time part
  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 
  -- Convert to DATE
  SELECT TO_DATE('2022-04-21 22:58:13', 'YYYY-MM-DD HH24:MI:SS') FROM dual; 
  # 2022-04-21 22:58:13

In Snowflake, you can use TO_TIMESTAMP to get the same result. Note that if you use the TO_DATE function it does not include the time part.

Snowflake:

  -- Convert to datetime
  SELECT TO_TIMESTAMP('2022-04-21 22:58:13', 'YYYY-MM-DD HH24:MI:SS');
  # 2022-04-21 22:58:13.000
 
  -- Convert to DATE (time part will be removed)
  SELECT TO_DATE('2022-04-21 22:58:13', 'YYYY-MM-DD HH24:MI:SS');
  # 2022-04-21

For more information, see Oracle to Snowflake Migration.