# Thread: how to calculate a date with sysdate and return with minute

1. Registered User
Join Date
Mar 2004
Location
THAILAND
Posts
15

## Unanswered: how to calculate a date with sysdate and return with minute

I'd like to calculate difference date between a date and sysdate and return with minute. ( Oracle Database ) ,please help me.

2. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Not sure what you mean by "return with minute". Say the given date is 23-AUG-2004 00:00:00 and SYSDATE = 24-AUG-2004 13:04:30. What do you want to see:

1) 1.54479167 days
2) 2224.5 minutes
3) 1 day, 13 hours and 4.5 minutes

... or something else?

3. Registered User
Join Date
Aug 2004
Posts
9
I Think he want to see the third one, but is this possible to get by date funktions ? .. I have a lot of experience in DB2 timestamp handling and calculating. DB2 is there a leader in time functionality, but exactly that is not possible.

how much minutes are bygone between time 0 and sysdate ? leap years are quiet importand for that, and specially the leap day in the years...

4. Registered User
Join Date
Aug 2004
Posts
9
shit .. all 3 items are the same

5. Registered User
Join Date
Mar 2004
Location
THAILAND
Posts
15

## thanks you

6. Registered User
Join Date
Jun 2003
Location
Ottawa
Posts
105

## by day

When performing date arithmetic, Oracle returns the amount in days. It would be a matter of multiplying up to minutes:

select ((sysdate - <date value>) * 24) * 60 from dual;

7. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171
Originally Posted by jupiter_111
Again, not sure what you mean. If you mean, show how to get all the 3 results I showed earlier then the first 2 are easy:

1) select sysdate-datecol from mytable;

2) select (sysdate-datecol)*24*60 from mytable;

The 3rd is more difficult. You could defien a function like this:
Code:
```SQL> CREATE OR REPLACE function days_to_time( p_days in integer ) return varchar2
2  is
3    d integer;
4    h integer;
5    m integer;
6  begin
7    d := floor(p_days);
8    h := floor((p_days-d)*24);
9    m := floor(((p_days-d)*24-h)*60);
10    return d||' days '||h||' hours and '||m||' minutes';
11* end;
SQL> /

Function created.

SQL> select days_to_time( (to_date('24-aug-2004 13:04:30','DD-MON-YYYY HH24:MI:SS')
2           - to_date('23-aug-2004','DD-MON-YYYY') ) )
3  from dual;

DAYS_TO_TIME((TO_DATE('24-AUG-200413:04:30','DD-MON-YYYYHH24:MI:SS')-TO_DATE('23
--------------------------------------------------------------------------------
1 days 13 hours and 4 minutes```

8. Registered User
Join Date
Apr 2004
Location
Posts
249
Hi,

The same result could be achieved by a single query:

HTML Code:
```select decode( sign(s.num-1), 1, trunc(num) ||' days, ', 'x') ||
round(mod(s.num,1)*24) ||' hours and ' ||
round(mod( mod(s.num,1)*24,1)*60,2) || ' minutes ' as interval
from (
SELECT sysdate - to_date('&dt', 'yyyy-mm-dd') num from dual
) s
/
```
For example, runing this query yields:

HTML Code:
``` SQL> r
select decode( sign(s.num-1), 1, trunc(num) ||' days, ', 'x') ||
round(mod(s.num,1)*24) ||' hours and ' ||
round(mod( mod(s.num,1)*24,1)*60,2) || ' minutes ' as interval
from (
SELECT sysdate - to_date('&dt', 'yyyy-mm-dd') num from dual
) s
Enter value for dt: 2004-08-23
old 5: SELECT sysdate - to_date('&dt', 'yyyy-mm-dd') num from dual
new 5: SELECT sysdate - to_date('2004-08-23', 'yyyy-mm-dd') num from dual
INTERVAL
--------------------------------------------------------------------------------
1 days, 13 hours and 57.4 minutes```
Thanks,
Ravi

9. Registered User
Join Date
Mar 2004
Location
THAILAND
Posts
15

## Many Thanks for all of you.

It's perfect.

Thank a lot

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•