Datetime functions

The following table describes the supported datetime functions.

Function	Description
ADD_MONTHS	Returns the date plus integer months. A month is defined by the NLS_CALENDAR session parameter. Accepts a datetime or a value implicitly convertible to DATE, and an integer or a value implicitly convertible to integer. Always returns DATE. If date is the last day of a month or the resulting month has fewer days, the result is the last day of that month; otherwise, the day component is preserved.
CURRENT_DATE	Returns the current date in the session time zone as a DATE value in the Gregorian calendar.
CURRENT_TIMESTAMP	Returns the current date and time in the session time zone as a TIMESTAMP WITH TIME ZONE. The offset reflects the SQL session’s local time. Defaults to precision 6 if omitted. Unlike LOCALTIMESTAMP, which returns a TIMESTAMP, this function includes the time zone.
DBTIMEZONE	Returns the value of the database time zone as either a time zone offset ([+ \| -]TZH:TZM) or a time zone region name, depending on how it was specified in the most recent CREATE DATABASE or ALTER DATABASE statement.
EXTRACT (datetime)	Returns the value of a specified datetime field from a datetime or interval expression.
FROM_TZ	Returns a TIMESTAMP WITH TIME ZONE by combining a TIMESTAMP value with a time zone. The time_zone_value must be a string in the format 'TZH:TZM' or a character expression in TZR format with an optional TZD.
LAST_DAY	Returns the date of the last day of the month that contains date, as defined by the NLS_CALENDAR session parameter. Always returns DATE, regardless of the data type of date.
LOCALTIMESTAMP	Returns the current date and time in the session time zone as a TIMESTAMP. Unlike CURRENT_TIMESTAMP, which returns a TIMESTAMP WITH TIME ZONE, this function does not include the time zone.
MONTHS_BETWEEN	Returns the number of months between date1 and date2, as defined by the NLS_CALENDAR parameter. The result is positive if date1 is later, negative if earlier. If both are the same day of the month or last days of their months, the result is an integer; otherwise, a fractional value is calculated based on a 31-day month and the difference in time components.
NEW_TIME	Returns the date and time in time zone timezone2 that corresponds to date in time zone timezone1. Requires NLS_DATE_FORMAT to be set for 24-hour time display. Always returns DATE, regardless of the data type of date.
NEXT_DAY	Returns the date of the first weekday named by char that follows date. The return type is always DATE. The char argument must be a day name or abbreviation in the session’s date language. The return value preserves the hours, minutes, and seconds of date.
NUMTODSINTERVAL	Returns an INTERVAL DAY TO SECOND literal by converting n to the specified unit. The argument n can be any NUMBER or an expression implicitly convertible to NUMBER. The interval\_unit must be CHAR, VARCHAR2, NCHAR, or NVARCHAR2 and resolve to a valid time unit string.
NUMTOYMINTERVAL	Returns an INTERVAL YEAR TO MONTH literal by converting n to the specified unit. The argument n can be any NUMBER or an expression implicitly convertible to NUMBER. The interval_unit must be CHAR, VARCHAR2, NCHAR, or NVARCHAR2 and resolve to a valid year or month unit string.
ORA_DST_AFFECTED	Returns 1 if the given TIMESTAMP WITH TIME ZONE value (or a VARRAY of such values) is affected by a time zone file change, resulting in a nonexisting time or duplicate time error. Otherwise, returns 0.
ORA_DST_CONVERT	Returns a converted TIMESTAMP WITH TIME ZONE value when the time zone file changes. Allows specifying error handling behavior for the given datetime expression.
ORA_DST_ERROR	Returns whether a TIMESTAMP WITH TIME ZONE value (or a VARRAY of such values) will result in an error with the new time zone data when the time zone file changes.
ROUND (date)	Returns date rounded to the unit specified by the format model fmt, using Gregorian calendar rules (not affected by NLS_CALENDAR). Always returns a DATE value, even if date is another datetime type. If fmt is omitted, rounds date to the nearest day.
SESSIONTIMEZONE	Returns the time zone of the current session as either a time zone offset ([+ \| -]TZH:TZM) or a time zone region name, depending on how it was specified in the most recent ALTER SESSION statement.
SYS_EXTRACT_UTC	Returns the UTC value extracted from a datetime with a time zone offset or region name. If no time zone is specified, the datetime is evaluated using the session time zone.
SYSDATE	Returns the current date and time of the operating system on which the database server runs. The return type is always DATE, with the display format controlled by the NLS_DATE_FORMAT parameter. In distributed SQL statements, returns the local database server’s operating system date and time. Cannot be used in a CHECK constraint condition.
SYSTIMESTAMP	Returns the system date with fractional seconds and time zone from the host operating system. The return type is TIMESTAMP WITH TIME ZONE.
TO_CHAR (datetime)	Converts a datetime or interval (DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL DAY TO SECOND, or INTERVAL YEAR TO MONTH) to a VARCHAR2 value using the date format model fmt.
TO_DSINTERVAL	Converts its argument to an INTERVAL DAY TO SECOND value.
TO_TIMESTAMP	Converts char to a TIMESTAMP value.
TO_TIMESTAMP_TZ	Converts char to a TIMESTAMP WITH TIME ZONE value.
TO_YMINTERVAL	Converts its argument to an INTERVAL YEAR TO MONTH value.
TRUNC (date)	Truncates date to the unit specified by the format model fmt, using Gregorian calendar rules (not affected by NLS_CALENDAR). Always returns a DATE value, even if date is another datetime type. If fmt is omitted, defaults to 'DD', truncating date to midnight of that day.
TZ_OFFSET	Returns the time zone offset for the specified argument at execution time. Accepts a valid time zone region name, a UTC offset (returned as is), or the keywords SESSIONTIMEZONE or DBTIMEZONE. Valid values for time_zone_name can be queried from the V$TIMEZONE_NAMES view.