Oracle Date-Time Functions
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose This page is a collection of built-in Oracle Database functions used to evaluate and manipulate dates. There are additional functions not listed here such as CASE and CAST you will find links to them at the bottom of the page.

Additional undocumented string related functions can be found on the Undocumented Oracle page also linked at page bottom.
Note
Have you seen the Functions page? If not ... Click Here ... for information on all Oracle functions
Dependencies
STANDARD    
 
+ AND -
+ <date> + <integer>
SELECT SYSDATE + 1 FROM dual;
- <date> - <integer>
SELECT SYSDATE - 1 FROM dual;
 
ADD_MONTHS
Add A Month To A Date ADD_MONTHS(left IN DATE, right IN NUMBER) RETURN DATE;
SELECT add_months(SYSDATE, 2) FROM dual;

-- but be aware of what it is doing
SELECT add_months(TO_DATE('27-JAN-2020'), 1) FROM dual;

SELECT add_months(TO_DATE('28-JAN-2020'), 1) FROM dual;

SELECT add_months(TO_DATE('29-JAN-2020'), 1) FROM dual;

SELECT add_months(TO_DATE('30-JAN-2020'), 1) FROM dual;

SELECT add_months(TO_DATE('31-JAN-2020'), 1) FROM dual;

SELECT add_months(TO_DATE('01-FEB-2020'), 1) FROM dual;
 
APPROX_MEDIAN
Returns absolutely nothing because the Oracle Docs are incorrect. Here's what they state: " It takes a numeric or datetime value and returns an approximate middle value or an approximate interpolated value...." Take a look at my demo: I say they are incorrect.

This is documented to help those that wonder what's wrong after reading the docs.
APPROX_MEDIAN(<expression> [DETERMINISTIC] [, <ERROR_RATE> | <CONFIDENCE>']) RETURN DATE;
SQL> SELECT default_tablespace, APPROX_MEDIAN(created DETERMINISTIC) ORAFAIL
  2  FROM dba_users
  3  GROUP BY default_tablespace
  4  ORDER BY 1;
SELECT default_tablespace, APPROX_MEDIAN(created DETERMINISTIC) MOD
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE
 
CURRENT_DATE
Returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE CURRENT_DATE RETURN DATE;
col sessiontimezone format a30

SELECT sessiontimezone, current_date
FROM dual;

ALTER SESSION SET TIME_ZONE = '-5:0';

SELECT sessiontimezone, current_date
FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT sessiontimezone, current_date
FROM dual;

ALTER SESSION SET TIME_ZONE = '-7:0';

SELECT sessiontimezone, current_date
FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
 
DUMP
Returns a VARCHAR2 value containing the datatype code, length in bytes, and internal representation of a value DUMP(
e   IN DATE,                 -- expression
df  IN PLS_INTEGER := NULL,  -- return format
sp  IN PLS_INTEGER := NULL,  -- starting position
len IN PLS_INTEGER := NULL)  -- length
RETURN VARCHAR2;

8 Octal
10 Decimal
16 Hexidecimal
17 Single Characters
1008 Octal notation with the character set name
1010 Decimal notation with the character set name
1016 Hexadecimal notation with the character set name
1017 Single characters with the character set name
col drows format a40

SELECT DUMP(SYSDATE) DROWS FROM dual;

SELECT DUMP(SYSDATE, 8) DROWS FROM dual;

SELECT DUMP(SYSDATE, 16) DROWS FROM dual;
 
EXTRACT
Extracts and returns the value of a specified datetime field from a datetime or interval value expression Values That Can Be Extracted:

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
TIMEZONE_HOUR
TIMEZONE_MINUTE
TIMEZONE_REGION
TIMEZONE_ABBR

EXTRACT (<type> FROM <datetime | interval>)
SELECT EXTRACT(YEAR FROM DATE '2009-03-15') FROM dual;

conn oe/oe@pdbdev

SELECT EXTRACT(hour FROM order_date) "Hour"
FROM orders;
 
GREATEST
Return the Latest Date GREATEST(pattern IN DATE) RETURN DATE;
CREATE TABLE t (
datecol1 DATE,
datecol2 DATE,
datecol3 DATE)
PCTFREE 0;

INSERT INTO t VALUES (SYSDATE+23, SYSDATE-10, SYSDATE-24);
INSERT INTO t VALUES (SYSDATE-15, SYSDATE, SYSDATE+15);
INSERT INTO t VALUES (SYSDATE-7, SYSDATE-18, SYSDATE-9);
COMMIT;

SELECT * FROM t;

SELECT GREATEST(datecol1, datecol2, datecol3)
FROM t;
 
INTERVAL
Interval to adjust date-time INTERVAL '<integer>' <unit>
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS')
FROM dual;

SELECT TO_CHAR(SYSDATE + INTERVAL '10' MINUTE, 'HH:MI:SS')
FROM dual;

SELECT TO_CHAR(SYSDATE - INTERVAL '10' MINUTE, 'HH:MI:SS')
FROM dual;
 
LAST_DAY
Returns The Last Date Of A Month LAST_DAY(right IN DATE) RETURN DATE;
SELECT * FROM t;

SELECT LAST_DAY(datecol1) FROM t;
 
LEAST
Return the Earliest Date LEAST(pattern IN DATE) RETURN DATE;
SELECT * FROM t;

SELECT LEAST(datecol1, datecol2, datecol3) FROM t;
 
LENGTH
Returns length in characters LENGTH(<date>) RETURN INTEGER;
SELECT LENGTH(last_ddl_time) FROM user_objects;
 
LENGTHB
Returns length in bytes LENGTHB(<date>) RETURN INTEGER;
SELECT LENGTHB(last_ddl_time) FROM user_objects;
 
LENGTHC
Returns length in characters LENGTHC(<date>) RETURN INTEGER;
SELECT LENGTHC(last_ddl_time) FROM user_objects;
 
LENGTH2
Returns length in UCS2 code points LENGTH2(<date>) RETURN INTEGER;
SELECT LENGTH2(last_ddl_time) FROM user_objects;
 
LENGTH4
Returns length in UCS4 code points LENGTH4(<date>) RETURN INTEGER;
SELECT LENGTH4(last_ddl_time) FROM user_objects;
 
MAX
Return the Latest Date MAX(<date>) RETURN INTEGER;
SELECT * FROM t;

SELECT MAX(datecol1) FROM t;
 
MIN
Return the Earliest Date MIN(<date>) RETURN INTEGER;
SELECT * FROM t;

SELECT MIN(datecol1) FROM t;
 
MONTHS_BETWEEN
Returns The Months Separating Two Dates MONTHS_BETWEEN(left IN DATE, right IN DATE) RETURN NUMBER;
SELECT MONTHS_BETWEEN(SYSDATE+365, SYSDATE-365) FROM dual;

SELECT MONTHS_BETWEEN(SYSDATE-365, SYSDATE+365) FROM dual;
 
NEW_TIME
Returns the date and time in time zone zone2 when date and time in time zone zone1 are date.

Before using this function, you must set the NLS_DATE_FORMAT parameter to display 24-hour time.
NEW_TIME(right IN DATE, middle IN VARCHAR2, left IN VARCHAR2) RETURN DATE;
SELECT NEW_TIME(TO_DATE('03-15-2020 01:23:45',
'MM-DD-YYYY HH24:MI:SS'), 'AST', 'PST') "New Date and Time"
FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT NEW_TIME(TO_DATE('03-15-2020 01:23:45',
'MM-DD-YYYY HH24:MI:SS'), 'AST', 'PST') "New Date and Time"
FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
 
NEXT_DAY
Date of next specified date following a date NEXT_DAY(left IN DATE, right IN VARCHAR2) RETURN DATE;

-- options are SUN, MON, TUE, WED, THU, FRI, and SAT
SELECT NEXT_DAY(SYSDATE, 'FRI') FROM dual;
 
ORA_DST_AFFECTED
Takes a datetime expression and returns 1 if the datetime value is affected by or will result in a "nonexisting time" or "duplicate time" error with the new time zone data: Otherwise 0 ORA_DST_AFFECTED(<date_time_expression>;
Follow DBMS_DST link at page bottom
 
ORA_DST_CONVERT
Allows specification of error handling for a specified datetime expression ORA_DST_CONVERT(<date_time_expression>;
Follow DBMS_DST link at page bottom
 
ORA_DST_ERROR
Resolves at datatime expression to a TIMESTAMP WITH TIME ZONE value or a VARRAY with TIMESTAMP WITH TIME ZONE values ORA_DST_ERROR(<date_time_expression>;
Follow DBMS_DST link at page bottom
 
ROUND
Returns date rounded to the unit specified by the format. If no format, the date is rounded to the nearest day

Overload 1
ROUND(right IN DATE) RETURN DATE;
SELECT ROUND(TO_DATE('10-FEB-20 10:10:10', 'DD-MON-RR HH24:MI:SS')) NEW_YEAR
FROM dual;
Returns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest day

Overload 2
ROUND(
left  IN DATE,
right IN VARCHAR2)
RETURN DATE;
SELECT ROUND(TO_DATE('27-JAN-2020'),'YEAR') NEW_YEAR
FROM dual;
 
Spelled Out Using TO_CHAR
Spelled Out Demo
DDSP HH24SP MISP MMSP SSSP
SELECT TO_CHAR(TO_DATE('10:30:18', 'HH24:MI:SS'), 'HH24SP:MISP:SSSP')
FROM dual;

SELECT TO_CHAR(TO_DATE('01-JAN-2020', 'DD-MON-YYYY'), 'DDSP-MONTH-YYYYSP')
FROM dual;

SELECT TO_CHAR(TO_DATE('01-JAN-2020', 'DD-MM-YYYY'), 'DDSP-MMSP-YYYYSP')
FROM dual;

conn scott/tiger@pdbdev

SELECT TO_CHAR(TO_DATE(sal,'J'), 'JSP')
FROM emp;
 
SYSDATE
Returns the current date and time set for the operating system on which the database resides SYSDATE RETURN DATE;
SELECT SYSDATE FROM dual;

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;
 
TO_CHAR
Converts a date to a formatted string
Day Month Year Fill Mode Julian Date
D MM YY FM J
DD MON YYYY    
DDTH   RR    
DAY   RRRR    
SELECT TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH:MI:SS') FROM dual;

SELECT TO_CHAR(SYSDATE, 'MONTH DAY RR HH:MI:SS') FROM dual;
 
TRUNC
Convert a date to the date at midnight

Overload 1
TRUNC(left IN DATE) RETURN DATE;
conn uwclass/uwclass@pdbdev

CREATE TABLE t (
datecol DATE);

INSERT INTO t (datecol) VALUES (SYSDATE);

INSERT INTO t (datecol) VALUES (TRUNC(SYSDATE));

INSERT INTO t (datecol) VALUES (TRUNC(SYSDATE, 'HH'));

INSERT INTO t (datecol) VALUES (TRUNC(SYSDATE, 'MI'));

COMMIT;

SELECT TO_CHAR(datecol, 'DD-MON-YYYY HH:MI:SS')
FROM t;
Selectively remove part of the date information

Overload 2

Special thanks to Dave Hayes for reminding me of this.
TRUNC(left IN DATE, right IN VARCHAR2) RETURN DATE;
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS')
FROM dual;

-- first day of the month
SELECT TO_CHAR(TRUNC(SYSDATE, 'MM'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;

SELECT TO_CHAR(TRUNC(SYSDATE, 'MON'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;

SELECT TO_CHAR(TRUNC(SYSDATE, 'MONTH'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;

-- first day of the year
SELECT TO_CHAR(TRUNC(SYSDATE, 'YYYY'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;

SELECT TO_CHAR(TRUNC(SYSDATE, 'YEAR'), 'DD-MON-YYYY HH:MI:SS')
FROM dual;
Dates in WHERE Clause Joins conn uwclass/uwclass@pdbdev

SELECT SYSDATE FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT SYSDATE FROM dual;

/

/

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

CREATE TABLE t (
datecol DATE);

INSERT INTO t
(datecol)
VALUES
(SYSDATE);

SELECT * FROM t;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT * FROM t;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

SELECT * FROM t;

SELECT SYSDATE FROM dual;

SELECT * FROM t
WHERE datecol = SYSDATE;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT * FROM t;

SELECT SYSDATE FROM dual;

SELECT TRUNC(SYSDATE) FROM dual;

SELECT * FROM t
WHERE TRUNC(datecol) = TRUNC(SYSDATE);

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
 
VSIZE
Returns The Number Of Bytes Required By A Value VSIZE(e IN DATE) RETURN NUMBER;
SELECT VSIZE(SYSDATE) FROM dual;
 
Date Calculations
Returns A Day A Specified Number Of Days In The Future Skipping Weekends CREATE OR REPLACE FUNCTION business_date (start_date DATE, Days2Add NUMBER)
AUTHID DEFINER RETURN DATE IS
 Counter  NATURAL := 0;
 CurDate  DATE := start_date;
 DayNum   POSITIVE;
 SkipCntr NATURAL := 0;
BEGIN
  WHILE Counter < Days2Add LOOP
    CurDate := CurDate+1;
    DayNum := TO_CHAR(CurDate, 'D');

    IF DayNum BETWEEN 2 AND 6 THEN
      Counter := Counter + 1;
    ELSE
      SkipCntr := SkipCntr + 1;
    END IF;
  END LOOP;
  RETURN start_date + Counter + SkipCntr;
END business_date;
/
Business Date function, above, enhanced by Larry Benton to handle negative values for the days2add parameter. CREATE OR REPLACE FUNCTION business_date (start_date DATE, days2add NUMBER)
AUTHID DEFINER RETURN DATE DETERMINISTIC AUTHID DEFINER IS
 Counter      NATURAL := 0;
 CurDate      DATE := start_date;
 DayNum       POSITIVE;
 SkipCntr     NATURAL := 0;
 Direction    INTEGER := 1;  -- days after start_date
 BusinessDays NUMBER := Days2Add;
BEGIN
  IF Days2Add < 0 THEN
    Direction := - 1; -- days before start_date
    BusinessDays := (-1) * BusinessDays;
  END IF;

  WHILE Counter < BusinessDays LOOP
    CurDate := CurDate + Direction;
    DayNum := TO_CHAR( CurDate, 'D');

    IF DayNum BETWEEN 2 AND 6 THEN
      Counter := Counter + 1;
    ELSE
      SkipCntr := SkipCntr + 1;
    END IF;
  END LOOP;

  RETURN start_date + (Direction * (Counter + SkipCntr));
END business_date;
/
Returns The First Day Of A Month CREATE OR REPLACE FUNCTION fday_ofmonth(value_in DATE)
AUTHID CURRENT_USER RETURN DATE DETERMINISTIC AUTHID DEFINER IS
 vMo VARCHAR2(2);
 vYr VARCHAR2(4);
BEGIN
  vMo := TO_CHAR(value_in, 'MM');
  vYr := TO_CHAR(value_in, 'YYYY');
  RETURN TO_DATE(vMo || '-01-' || vYr, 'MM-DD-YYYY');
EXCEPTION
  WHEN OTHERS THEN
    RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');
END fday_ofmonth;
/
 
Time Calculations
Returns The Number Of Seconds Between Two Date-Time Values CREATE OR REPLACE FUNCTION time_diff (DATE_1 IN DATE, DATE_2 IN DATE)
AUTHID CURRENT_USER RETURN NUMBER IS
 NDATE_1   NUMBER;
 NDATE_2   NUMBER;
 NSECOND_1 NUMBER(5,0);
 NSECOND_2 NUMBER(5,0);
BEGIN
  -- return Julian date number from first date (DATE_1)
  NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

  -- return Julian date number from second date (DATE_2)
  NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

  -- return seconds since midnight from first date (DATE_1)
  NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

  -- return seconds since midnight from second date (DATE_2)
  NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

  RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
/
Calculate time from seconds
Posted by John K. Hinsdale 12/30/06 to c.d.o.misc
SELECT DECODE(FLOOR(999999/86400), 0, '', FLOOR(999999/86400) || ' day(s), ') ||
       TO_CHAR(TO_DATE(MOD(999999, 86400),'SSSSS'), 'HH24:MI:SS') AS elapsed
FROM dual;
Obtain counts per ten minute increment

Posted by Michele Cadot 03/09/08 to c.d.o.misc
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT TRUNC(SYSDATE) + dbms_random.value(0,86400)/86400
FROM dual
CONNECT BY LEVEL <= 10;

WITH data AS (
  SELECT TRUNC(SYSDATE)+dbms_random.value(0,86400)/86400 h
  FROM dual
  CONNECT BY LEVEL <= 10)
SELECT TO_CHAR(h,'DD/MM/YYYY HH24:MI:SS') h, TO_CHAR(TRUNC(h) +
       TRUNC(TO_CHAR(h,'SSSSS')/600)/144, 'DD/MM/YYYY HH24:MI:SS') "10m"
FROM data
ORDER BY h;

Related Topics
Analytic Functions
Built-in Functions
CASE
CAST
Character Functions
Collection Functions
Conversion Functions
Data Mining Functions
DBMS_DST
Miscellaneous Functions
Numeric Functions
OLAP Functions
Operators (Built-in)
Regular Expressions
REPLACE
String Functions
SUBSTRING
Timestamp Functions
TRANSLATE
Undocumented Oracle
WMSYS Operators
XML Functions
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx