Home » SQL » Oracle Date Functions | List of Quick Examples

Oracle Date Functions | List of Quick Examples

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

Oracle Date Functions

Function Description Example
ADD_MONTHS The ADD_MONTHS procedure returns the input date plus or minus the number of months specified Select Add_Months
(’12-Jan-2018′, 3) From Dual;

Output: 12-Apr-2018

CURRENT_DATE CURRENT_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_TIMESTAMP CURRENT_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

DBTIMEZONE The 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_TZ The 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_DAY The 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

LOCALTIMESTAMP The 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_BETWEEN The 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_TIME The 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_DAY NEXT_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

NUMTODSINTERVAL NUMTODSINTERVAL converts n to an INTERVAL DAY TO SECOND literal. Select Numtodsinterval (22, ‘Day’) From Dual;

Output: +22 00:00:00.000000

NUMTOYMINTERVAL NUMTOYMINTERVAL 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

SESSIONTIMEZONE The SESSIONTIMEZONE function returns the value of the current session’s time zone. Select Sessiontimezone From
Dual;

Output: +08:00

SYS_EXTRACT_UTC The 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

SYSDATE The SYSDATE function returns the current system date and time. Select Sysdate From Dual;

Output: 03/09/2018 21:22:12

SYSTIMESTAMP The 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_TIMESTAMP TO_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_TZ TO_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_DSINTERVAL TO_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_YMINTERVAL The 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_OFFSET TZ_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: