Press "Enter" to skip to content

Oracle Date Functions | List of Quick Examples

Here is the list of Oracle Date Functions with their descriptions and quick examples.

Oracle Date Functions

FunctionDescriptionExample
ADD_MONTHSThe ADD_MONTHS procedure returns the input date plus or minus the number of months specifiedSelect Add_Months
(’12-Jan-2018′, 3) From Dual;

Output: 12-Apr-2018
CURRENT_DATECURRENT_DATE returns the current date in the session time zone, in value in the Gregorian calendar of datatype DATE.Select Current_Date From Dual;

Output: 03/09/2018 20:00:03
CURRENT_TIMESTAMPCURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE.Select
Current_Timestamp From Dual;

Output: 03/09/2018
20:00:35.815562 +08:00
DBTIMEZONEThe DBTIMEZONE function returns the value of the database time zone.Select Dbtimezone From Dual;

Output: +08:00
EXTRACT (datetime)An EXTRACT datetime function extracts and returns the value of a specified datetime field from a datetime or interval value expression.Select Extract(Year From Date ‘2018-03-07’) From Dual;

Output: 2018
FROM_TZThe FROM_TZ function converts a timestamp value at a time zone to a TIMESTAMP WITH TIME ZONE value.Select From_Tz(Timestamp ‘2018-04-12 08:00:00’, ‘3:00’) From Dual;

Output: 28/03/2000
08:00:00.000000000 +03:00
LAST_DAYThe LAST_DAY function returns the last day of the month for the specified date.Select Last_Day (’12-Jan-2018′) From Dual;

Output: 31/01/2018
LOCALTIMESTAMPThe LOCALTIMESTAMP function returns the current date and time in the session time zone in a value of datatype TIMESTAMP.Select Localtimestamp From Dual;

Output: 04/09/2018
14:07:50.489370
MONTHS_BETWEENThe MONTHS_BETWEEN function accepts two dates and returns the number of months between them.Select Months_Between (’31-Dec-2018′, ’31-Mar-2018′) From Dual;

Output: 9
NEW_TIMEThe NEW_TIME function generates a new date based on a given date and time zone and an input time zone.Select To_Char (

New_Time (To_Date (‘01152018 12:30 Am’, ‘Mmddyyyy Hh:Mi
Am’), ‘Cst’, ‘Hdt’), ‘Month Dd, Yyyy Hh:Mi Am’) From Dual;

Output: January 14, 2018 09:30 Pm
NEXT_DAYNEXT_DAY returns the date of the first weekday named by char that is later than the date.Select Next_Day (’01-Sep-2018′, ‘Monday’) From Dual;

Output: 03/09/2018
NUMTODSINTERVALNUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND literal.Select Numtodsinterval (22, ‘Day’) From Dual;

Output: +22 00:00:00.000000
NUMTOYMINTERVALNUMTOYMINTERVAL converts number n to an INTERVAL YEAR TO MONTH literal.Select NUMTOYMINTERVAL (8, ‘MONTH’) FROM DUAL;

Output: +00-08
ROUND (date)The ROUND function rounds a date value to the nearest date as specified by a format mask.SELECT ROUND (TO_DATE (’02sep2018′), ‘W’) FROM DUAL;

OUTPUT: 01/09/2018
SESSIONTIMEZONEThe SESSIONTIMEZONE function returns the value of the current session’s time zone.Select Sessiontimezone From
Dual;

Output: +08:00
SYS_EXTRACT_UTCThe SYS_EXTRACT_UTC function extracts the UTC (Coordinated Universal Time–formerly Greenwich Mean Time) from a datetime with time zone
displacement.
Select Sys_Extract_Utc(Timestamp ‘2018-03-28 11:30:00.00 -08:00’) From Dual;

Output: 28/03/2018 19:30:00.000000000
SYSDATEThe SYSDATE function returns the current system date and time.Select Sysdate From Dual;

Output: 03/09/2018 21:22:12
SYSTIMESTAMPThe SYSTIMESTAMP function returns the system date, including fractional seconds and time zone of the database.Select Systimestamp From Dual;

Output: 03/09/2018
21:22:51.075340 +08:00
TO_CHAR (datetime)TO_CHAR converts the date of DATE, TIMESTAMP, TIMESTAMP
WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt.
Select
To_Char (Sysdate, ‘Month Dd, Yyyy’) From Dual;

Output: September 03, 2018
TO_TIMESTAMPTO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.Select To_Timestamp (‘2018-12-01 11:00:00’, ‘Yyyy-Mm-Dd Hh:Mi:Ss’) From Dual;

Output: 01/12/2018 11:00:00.000000000
TO_TIMESTAMP_TZTO_TIMESTAMP_TZ converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP WITH TIME ZONE datatype.Select To_Timestamp_Tz(‘2018-12-01 11:00:00 -8:00’,‘Yyyy-Mm-Dd Hh:Mi:Ss Tzh:Tzm’) From Dual;

Output: 01/12/2018
11:00:00.000000000 -08:00
TO_DSINTERVALTO_DSINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL DAY TO SECOND type.Select
To_Dsinterval (‘100 10:00:00’) From Dual;

Output: +100 10:00:00.000000
TO_YMINTERVALThe TO_YMINTERVAL function converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL YEAR TO MONTH
type, where char is the character string to be converted.
Select To_Yminterval(’01-02′) From Dual;

Output: +01-02
TRUNC (date)The TRUNC function truncates a date as specified by a format mask.SELECT
TRUNC (SYSDATE, ‘Mm’) FROM DUAL;

Output: 01/09/2018
TZ_OFFSETTZ_OFFSET returns the time zone offset corresponding to the value entered based on the date the statement execution.SELECT TZ_OFFSET(‘US/EASTERN’) Offset FROM Dual;

Output: -04:00

See also: