Oracle Datetime functions

Last update on August 19 2022 21:50:41 (UTC/GMT +8 hours)

Description

Datetime functions operate on a date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) values.

Here is the list of datetime functions:

Name	Description
ADD_MONTHS	ADD_MONTHS returns a date (date plus integer months).
CURRENT_DATE	CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.
CURRENT_TIMESTAMP	The CURRENT_TIMESTAMP() function returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE.
DBTIMEZONE	DBTIMEZONE returns the value of the database time zone.
EXTRACT(datetime)	EXTRACT() function returns extract value of a specified datetime field from a datetime or interval expression.
FROM_TZ	FROM_TZ() function converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value.
LAST_DAY	LAST_DAY() function returns the date of the last day of the month that contains a date.
LOCALTIMESTAMP	LOCALTIMESTAMP() function returns the current date and time in the session time zone in a value of datatype TIMESTAMP.
MONTHS_BETWEEN	MONTHS_BETWEEN() function returns the number of months between dates (date1, date2).
NEW_TIME	NEW_TIME() function converts a date from timezone1 to a date in timezone2.
NEXT_DAY	NEXT_DAY returns the date of the first weekday that is later than the date.
NUMTODSINTERVAL	NUMTODSINTERVAL() function returns a number to an INTERVAL DAY TO SECOND literal.
NUMTOYMINTERVAL	NUMTOYMINTERVAL() function is used to convert a number to an INTERVAL YEAR TO MONTH literal.
ROUND(date)	ROUND() returns the date rounded to the unit specified by the format model.
SESSIONTIMEZONE	SESSIONTIMEZONE function is used to get the time zone of the current session.
SYS_EXTRACT_UTC	SYS_EXTRACT_UTC() returns the UTC (Coordinated Universal Time�formerly Greenwich Mean Time) from a datetime value with time zone offset or time zone region name.
SYSDATE	SYSDATE returns the current date and time.
SYSTIMESTAMP	SYSTIMESTAMP function returns the system date, including fractional seconds and time zone.
TO_CHAR(datetime)	TO_CHAR (datetime) function returns a datetime or interval value of DATE. TIMESTAMP, TIMESTAMP WITH TIME ZONE.
TO_DSINTERVAL	TO_DSINTERVAL() function returns a character string of (CHAR, VARCHAR2, NCHAR, or NVARCHAR2) datatype to an INTERVAL DAY TO SECOND type.
TO_TIMESTAMP	TO_TIMESTAMP() function returns converts CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.
TO_TIMESTAMP_TZ	TO_TIMESTAMP_TZ converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP WITH TIME ZONE datatype.
TO_YMINTERVAL	TO_YMINTERVAL() function converts a character string ( CHAR, VARCHAR2, NCHAR, or NVARCHAR2) datatype to an INTERVAL YEAR TO MONTH type.
TRUNK(date)	The TRUNC (date) function returns the date with the time portion of the day truncated to a specific unit of measure.
TZ_OFFSET	TZ_OFFSET() function returns the time zone offset corresponding to the argument based on the date the statement is executed

Previous: WIDTH_BUCKET
Next: ADD_MONTHS