Oracle Datetime Functions

This page summarizes common date and time functions in Oracle. Date and time functions operate on dates (DATE), timestamps (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) values.

  1. ADD_MONTHS

    Oracle ADD_MONTHS() is a built-in function used to add or subtract a specified number of months to a given date.

  2. CURRENT_DATE

    Oracle CURRENT_DATE is a built-in function that returns the current date in the current session time zone as a value of the DATE data type in the Gregorian calendar.

  3. CURRENT_TIMESTAMP

    Oracle CURRENT_TIMESTAMP() is a built-in function that returns the current date and time in the current session time zone, as a value of the TIMESTAMP WITH TIME ZONE data type.

  4. DBTIMEZONE

    Oracle DBTIMEZONE is a built-in function that returns the value of the database timezone.

  5. EXTRACT(datetime)

    Oracle EXTRACT(datetime) is a built-in function that extracts and returns the value of the specified date field from a datetime or interval expression.

  6. FROM_TZ

    Oracle FROM_TZ() is a built-in function that converts a timestamp value and a time zone value into a timestamp value with time zone.

  7. LAST_DAY

    Oracle LAST_DAY() is a built-in function that returns the last day of the month for a given date.

  8. LOCALTIMESTAMP

    Oracle LOCALTIMESTAMP() is a built-in function that returns the current date and time in the current session time zone as a TIMESTAMP data type.

  9. MONTHS_BETWEEN

    The Oracle MONTHS_BETWEEN() is a built-in function that returns the number of months between two given dates.

  10. NEW_TIME

    Oracle NEW_TIME() is a built-in function that converts a date from one specified time zone to another specified time zone and returns the result.

  11. NEXT_DAY

    Oracle NEXT_DAY() is a built-in function that returns the date of the specified first workday that occurs after a given date.

  12. ORA_DST_AFFECTED

    Oracle ORA_DST_AFFECTED() is a built-in function that checks whether the given datetime expression is affected by the modification of the time zone file.

  13. ORA_DST_CONVERT

    Oracle ORA_DST_CONVERT() is a built-in function that converts a given date-time expression when modifying time zone files and allows you to specify how to handle errors.

  14. ORA_DST_ERROR

    Oracle ORA_DST_ERROR() is a built-in function that returns a number indicating whether the given date-time value would cause errors with the new time zone data.

  15. ROUND(date)

    Oracle ROUND(date) is a built-in function that rounds the given date to the specified unit.

  16. SESSIONTIMEZONE

    Oracle SESSIONTIMEZONE() is a built-in function that returns the value of the time zone for the current session.

  17. SYS_EXTRACT_UTC

    Oracle SYS_EXTRACT_UTC() is a built-in function that extracts the UTC from a datetime value with a time zone offset or time zone name.

  18. SYSDATE

    Oracle SYSDATE is a built-in function that returns the current date and time as set in the operating system of the database server.

  19. SYSTIMESTAMP

    Oracle SYSTIMESTAMP is a built-in function that returns the current date and time, including fractional seconds and time zone, as set by the operating system on the database server.

  20. TRUNC(DATE)

    Oracle TRUNC(date) is a built-in function that truncates a given date to the specified unit.

  21. TZ_OFFSET

    Oracle TZ_OFFSET() is a built-in function that returns the time zone offset corresponding to the provided parameter, based on the date of the statement execution.