Discussions
Categories
 17.9K All Categories
 3.4K Industry Applications
 3.4K Intelligent Advisor
 73 Insurance
 537.3K OnPremises Infrastructure
 138.6K Analytics Software
 38.6K Application Development Software
 6K Cloud Platform
 109.6K Database Software
 17.6K Enterprise Manager
 8.8K Hardware
 71.2K Infrastructure Software
 105.3K Integration
 41.6K Security Software
Idea : Date functions which will give output as days, years, minutes, seconds etc...
Hi All,
This idea is to have a function for date which will take input as three parameters.
name of the function can be DIFF_DATES (DATE1 WITH TIME STAMP, DATE2 WITH TIME STAMP, OPTIONAL PARAMETER) > If we pass input as date1 and date2 and third paramter as 'D' then the function should return output as difference between two dates in days.
below table will give a clear idea:
Parameter1  Parameter2  Parameter  Output 

23MAY2016  22MAY2016  DAYS  1D 
23MAY2016  22APR2016  MONTHS  1 Month / 30 days 
23MAY2016  22MAY2015  YEARS  1 year / 12 months / 365 days 
23MAY2016 22:10:00  23MAY2016 22:40:15  MINUTES  20MINUTES 15 SECONDS 
This queries were asked several times by many of the developers
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:96012348060
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5931515900346288940
Please let us know whether you would like to have support for SQL date function which will help in reducing the customized functions and also as this is the most important and used function in a day to day life. @connor
Comments

Just because this is an often asked question doesn't mean that there is no answer for it yet.
The differences between two date values can already be shown.
But date and calendar arithmethic is not easy. That is something many developers need to understand first.
Currently we can do
A  B => number of days with fractionals beeing hours, minutes, seconds
months_between (A, B ) => Number of months (=years). Fractionals are hard to correctly interpret in this case.
The first logic uses the daysecond range , the second function works in the monthyear range. With some specialized and documented logic for end of month calculations.
I don't see that yet another function would provide any significant advantage.

Just because this is an often asked question doesn't mean that there is no answer for it yet.
The differences between two date values can already be shown.
But date and calendar arithmethic is not easy. That is something many developers need to understand first.
Currently we can do
A  B => number of days with fractionals beeing hours, minutes, seconds
months_between (A, B ) => Number of months (=years). Fractionals are hard to correctly interpret in this case.
The first logic uses the daysecond range , the second function works in the monthyear range. With some specialized and documented logic for end of month calculations.
I don't see that yet another function would provide any significant advantage.
Agreed Sven,
Product should also support / consider what is most frequently used in implementations. at the moment every one have their own version of the details. Thought of sharing where we have one function which will give the result based on operator.
on that front, substr and instr function will give the result (with multiple iterations, results) what regexpsubstr will give, then why oracle has introduced this functions. there is a reason for it.
It will definitely provide advantage with the native sql function in such case.

What have already is quite powerful, so you can combine any of these to get what you want:
select INTERVAL'20' DAY  INTERVAL'240' HOUR from dual;
SELECT EXTRACT(YEAR FROM DATE '19980307') FROM DUAL;
SELECT EXTRACT(DAY FROM sysdate) FROM DUAL;
SELECT NUMTODSINTERVAL(7.251, 'HOUR') FROM DUAL;
SELECT EXTRACT(HOUR FROM NUMTODSINTERVAL(7.251, 'HOUR')) FROM DUAL;
SELECT EXTRACT(MINUTE FROM NUMTODSINTERVAL(7.251, 'HOUR')) FROM DUAL;
 eg 10Jan2016 22:43:53 +000000063 02:27:45.000000000
select min(COMPLETION_TIME), NUMTODSINTERVAL(sysdatemin(COMPLETION_TIME),'DAY')
from v$archived_log;
 nicer duration format
 eg 10Jan2016 22:43:53 +063 02:26:01
select min(COMPLETION_TIME), (sysdatemin(COMPLETION_TIME)) DAY(3) TO SECOND(0)
from v$archived_log;

You may find it an essential function that you need every day, but I've never really come across the need for it. This is why we have userdefined functions, e.g.
create or replace function date_diff ( p_date1 date , p_date2 date , p_unit varchar2 default 'DAYS' ) return number deterministic is begin return case upper(nvl(p_unit,'DAYS')) when 'DAYS' then p_date2  p_date1 when 'HOURS' then (p_date2  p_date1) * 24 when 'MINUTES' then (p_date2  p_date1) * 1440 when 'SECONDS' then (p_date2  p_date1) * 86400 when 'MONTHS' then months_between(p_date1,p_date1) when 'YEARS' then months_between(p_date1,p_date1) / 12 end; end date_diff;

You may find it an essential function that you need every day, but I've never really come across the need for it. This is why we have userdefined functions, e.g.
create or replace function date_diff ( p_date1 date , p_date2 date , p_unit varchar2 default 'DAYS' ) return number deterministic is begin return case upper(nvl(p_unit,'DAYS')) when 'DAYS' then p_date2  p_date1 when 'HOURS' then (p_date2  p_date1) * 24 when 'MINUTES' then (p_date2  p_date1) * 1440 when 'SECONDS' then (p_date2  p_date1) * 86400 when 'MONTHS' then months_between(p_date1,p_date1) when 'YEARS' then months_between(p_date1,p_date1) / 12 end; end date_diff;
Thanks William, it all depends on what requirements we are working on to encounter the situation.