## Forum Stats

• 3,874,727 Users
• 2,266,768 Discussions

Discussions

# Idea : Date functions which will give output as days, years, minutes, seconds etc...

edited May 23, 2016 7:04AM

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:

Parameter1Parameter2ParameterOutput
23-MAY-201622-MAY-2016DAYS1D
23-MAY-201622-APR-2016MONTHS1 Month / 30 days
23-MAY-201622-MAY-2015YEARS1 year / 12 months / 365 days
23-MAY-2016 22:10:0023-MAY-2016 22:40:15MINUTES20MINUTES 15 SECONDS

This queries were asked several times by many of the developers

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

-4

#### Active · Last Updated May 23, 2016 7:04AM

• Member Posts: 10,559 Gold Crown
edited May 23, 2016 10:00AM

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 day-second range , the second function works in the month-year 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.

• Member Posts: 602 Bronze Badge

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 day-second range , the second function works in the month-year 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 regexp-substr 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.

• Member Posts: 3,666 Gold Crown

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 '1998-03-07') 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(sysdate-min(COMPLETION_TIME),'DAY')

from v\$archived_log;

-- nicer duration format

-- eg 10Jan2016 22:43:53 +063 02:26:01

select min(COMPLETION_TIME), (sysdate-min(COMPLETION_TIME)) DAY(3) TO SECOND(0)

from v\$archived_log;

• Member Posts: 9,568 Bronze Crown
edited May 24, 2016 9:21AM

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 user-defined 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;
```
• Member Posts: 602 Bronze Badge

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 user-defined 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.