https://www.sqlines.com/ 2026-05-01T06:20:21+00:00 https://www.sqlines.com/ https://www.sqlines.com/lib/images/favicon.ico text/html 2022-04-24T18:54:37+00:00 https://www.sqlines.com/oracle-to-snowflake/datetime_arithmetic?rev=1650826477&do=diff Datetime arithmetic involves addition (+) and subtraction (-) operators on date and time values. Add and Subtract Days In Oracle, if you use the (+) operator to add an integer value to a datetime, you add days: Oracle: ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; -- Add 3 days to the current day SELECT SYSDATE + 3 FROM dual; # 2022-04-27 21:24:13 text/html 2022-04-22T14:02:57+00:00 https://www.sqlines.com/oracle-to-snowflake/instr?rev=1650636177&do=diff In Oracle the INSTR function allows you to find the position of substring in a string. It accepts 2, 3 or 4 parameters. In Snowflake you have to use the REGEXP_INSTR function that also accepts 2, 3 or 4 parameters. INSTR with 2 Parameters - Search from Beginning INSTR with 2 parameters searches the specified substring from the beginning of string: text/html 2022-04-23T10:37:19+00:00 https://www.sqlines.com/oracle-to-snowflake/months_between?rev=1650710239&do=diff In Oracle, the MONTHS_BETWEEN(date1, date2) function returns the number of months between two dates as a decimal number. Snowflake also provides the MONTHS_BETWEEN function. Oracle: -- The same day of 2 different months SELECT MONTHS_BETWEEN(DATE '2022-03-28', DATE '2022-02-28') FROM dual; # 1 -- The last day of 2 different months (compare with the previous example, the result is the same) SELECT MONTHS_BETWEEN(DATE '2022-03-31', DATE '2022-02-28') FROM dual; # 1 -- 1-day … text/html 2022-04-21T20:02:51+00:00 https://www.sqlines.com/oracle-to-snowflake/sysdate?rev=1650571371&do=diff 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 text/html 2022-04-23T08:44:00+00:00 https://www.sqlines.com/oracle-to-snowflake/to_char_datetime?rev=1650703440&do=diff In Oracle, the TO_CHAR function allows you to convert a DATE or TIMESTAMP value a string using the specified datetime format. Snowflake also provides the TO_CHAR function. Oracle: -- Convert the current datetime to string SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24') FROM dual; # 04-23-2022 11 text/html 2022-04-22T16:32:51+00:00 https://www.sqlines.com/oracle-to-snowflake/to_date?rev=1650645171&do=diff 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. text/html 2022-04-23T12:46:43+00:00 https://www.sqlines.com/oracle-to-snowflake/trunc_datetime?rev=1650718003&do=diff In Oracle, TRUNC(datetime, unit) function allows you to truncate a datetime value to the specified unit (set zero time, set the first day of the month i.e). The default unit is 'DD' (truncation to a day). Snowflake also provides the TRUNC function, but it requires the unit to be specified (there is no default value) and unit values are different in Oracle and Snowflake.