Menu

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

    Learn Oracle ADD_MONTHS(date, integer), including month-end adjustment, negative offsets, NLS_CALENDAR, and its DATE return type.

  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 attaches a time zone to a TIMESTAMP and returns TIMESTAMP WITH TIME ZONE. See offset, region, NULL, DST, and conversion examples.

  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 converts a datetime value to UTC. Learn its TIMESTAMP return type, session time-zone behavior, and practical examples.

  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)

    Learn Oracle TRUNC(datetime) for removing time, finding month or year starts, truncating ISO weeks, handling TIMESTAMP inputs, and filtering dates efficiently.

  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.