PL/SQL - Date & Time

PL/SQL - Date & Time

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

ADD_MONTHS

ADD_MONTHS( DATE '2016-02-29', 1 )

31-MAR-16
Add 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:44
Return the current date and time in the session time zone

CURRENT_TIMESTAMP

SELECT CURRENT_TIMESTAMP FROM dual

06-AUG-17 08.26.52.742000000 PM -07:00
Return the current date and time with time zone in the session time zone

DBTIMEZONE

 SELECT DBTIMEZONE FROM dual;
 

-07:00
Get the current database time zone

EXTRACT

EXTRACT(YEAR FROM SYSDATE)

2017
Extract a value of a date time field e.g., YEAR, MONTH, DAY, … from a date time value.

FROM_TZ

FROM_TZ(TIMESTAMP '2017-08-08 08:09:10', '-09:00')

08-AUG-17 08.09.10.000000000 AM -07:00
Convert a timestamp and a time zone to a TIMESTAMP WITH TIME ZONE value

LAST_DAY

LAST_DAY(DATE '2016-02-01')

29-FEB-16
Gets the last day of the month of a specified date.

LOCALTIMESTAMP

SELECT LOCALTIMESTAMP FROM dual

06-AUG-17 08.26.52.742000000 PM
Return a TIMESTAMP value that represents the current date and time in the session time zone.

MONTHS_BETWEEN

MONTHS_BETWEEN( DATE '2017-07-01', DATE '2017-01-01' )

6
Return the number of months between two dates.

NEW_TIME

NEW_TIME( TO_DATE( '08-07-2017 01:30:45', 'MM-DD-YYYY HH24:MI:SS' ), 'AST', 'PST' )

06-AUG-2017 21:30:45
Convert a date in one time zone to another

NEXT_DAY

NEXT_DAY( DATE '2000-01-01', 'SUNDAY' )

02-JAN-00
Get the first weekday that is later than a specified date.

ROUND

ROUND(DATE '2017-07-16', 'MM')

01-AUG-17
Return a date rounded to a specific unit of measure.

SESSIONTIMEZONE

 SELECT SESSIONTIMEZONE FROM dual;
 

-07:00
Get the session time zone

SYSDATE

 SYSDATE
 

01-AUG-17
Return 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:00
Return the system date and time that includes fractional seconds and time zone.

TO_CHAR

 TO_CHAR( DATE'2017-01-01', 'DL' )
 

Sunday, January 01, 2017
Convert a DATE or an INTERVAL value to a character string in a specified format.

TO_DATE

TO_DATE( '01 Jan 2017', 'DD MON YYYY' )

01-JAN-17
Convert a date which is in the character string to a DATE value.

TRUNC

 TRUNC(DATE '2017-07-16', 'MM')
 

01-JUL-17
Return a date truncated to a specific unit of measure.

TZ_OFFSET

TZ_OFFSET( 'Europe/London' )

+01:00
Get time zone offset of a time zone name from UTC