Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Date Functions

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?
  • YesNo
Previous Oracle Analytic Functions
Next Oracle String Functions

Oracle Functions

  • Aggregate Functions
  • Analytic Functions
  • Comparison Functions
  • Date Functions
  • String Functions

Oracle Date Functions

  • ADD_MONTHS
  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • DBTIMEZONE
  • EXTRACT
  • FROM_TZ
  • LAST_DAY
  • LOCALTIMESTAMP
  • MONTHS_BETWEEN
  • NEXT_DAY
  • NEW_TIME
  • ROUND
  • SESSIONTIMEZONE
  • SYSDATE
  • SYSTIMESTAMP
  • TO_CHAR
  • TO_DATE
  • TRUNC
  • TZ_OFFSET

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.