Oracle | Date and Time Functions

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;

Morgan's Library Page Footer

This site is maintained by Dan Morgan. Last Updated:

DBSecWorx