Oracle Date Functions

This page provides you with the most commonly used Oracle date functions that help you handle date and time data easily and more effectively.

FunctionExampleResultDescription
ADD_MONTHSADD_MONTHS( DATE '2016-02-29', 1 ) 31-MAR-16Add a number of months (n) to a date and return the same day which is n of months away.
CURRENT_DATE SELECT CURRENT_DATE FROM dual  06-AUG-2017 19:43:44Return the current date and time in the session time zone
CURRENT_TIMESTAMPSELECT CURRENT_TIMESTAMP FROM dual06-AUG-17 08.26.52.742000000 PM -07:00Return the current date and time with time zone in the session time zone
DBTIMEZONE SELECT DBTIMEZONE FROM dual; -07:00Get the current database time zone
EXTRACTEXTRACT(YEAR FROM SYSDATE) 2017Extract a value of a date time field e.g., YEAR, MONTH, DAY, … from a date time value.
FROM_TZFROM_TZ(TIMESTAMP '2017-08-08 08:09:10', '-09:00') 08-AUG-17 08.09.10.000000000 AM -07:00Convert a timestamp and a time zone to a TIMESTAMP WITH TIME ZONE value
LAST_DAYLAST_DAY(DATE '2016-02-01') 29-FEB-16Gets the last day of the month of a specified date.
LOCALTIMESTAMPSELECT LOCALTIMESTAMP FROM dual 06-AUG-17 08.26.52.742000000 PMReturn a TIMESTAMP value that represents the current date and time in the session time zone.
MONTHS_BETWEENMONTHS_BETWEEN( DATE '2017-07-01', DATE '2017-01-01' )6Return the number of months between two dates.
NEW_TIMENEW_TIME( TO_DATE( '08-07-2017 01:30:45', 'MM-DD-YYYY HH24:MI:SS' ), 'AST', 'PST' ) 06-AUG-2017 21:30:45Convert a date in one time zone to another
NEXT_DAYNEXT_DAY( DATE '2000-01-01', 'SUNDAY' ) 02-JAN-00Get the first weekday that is later than a specified date.
ROUNDROUND(DATE '2017-07-16', 'MM') 01-AUG-17Return a date rounded to a specific unit of measure.
SESSIONTIMEZONE SELECT SESSIONTIMEZONE FROM dual;  -07:00Get the session time zone
SYSDATE SYSDATE  01-AUG-17Return the current system date and time of the operating system where the Oracle Database resides.
SYSTIMESTAMP SELECT SYSTIMESTAMP FROM dual;01-AUG-17 01.33.57.929000000 PM -07:00Return the system date and time that includes fractional seconds and time zone.
TO_CHAR TO_CHAR( DATE'2017-01-01', 'DL' ) Sunday, January 01, 2017Convert a DATE or an INTERVAL value to a character string in a specified format.
TO_DATETO_DATE( '01 Jan 2017', 'DD MON YYYY' ) 01-JAN-17Convert a date which is in the character string to a DATE value.
TRUNC TRUNC(DATE '2017-07-16', 'MM') 01-JUL-17Return a date truncated to a specific unit of measure.
TZ_OFFSETTZ_OFFSET( 'Europe/London' ) +01:00Get time zone offset of a time zone name from UTC
Was this tutorial helpful?