Re: Is there a dateadd() function?

From: Arjan van Bentem <avbentem_at_DONT-YOU-DARE-cable.a2000.nl>
Date: Tue, 21 Jul 1998 22:12:04 +0200
Message-ID: <6p2smc$ia9$1@newton.a2000.nl>

Easy enough:

(date2 - date1) = days
(date2 - date1) * 24 = hours

Get the picture?

(date2 - date1) * 24 * 60 = minutes
(date2 - date1) * 24 * 60 * 60 = seconds

By the way, to print the difference in readable HH:MI:SS format (where HH could be > 23 if the difference is more than one day), try something like

    to_char( trunc( (date2 - date1) * 24 ) || ':' ||
    to_char( mod( abs(date2 - date1) * 24 * 60, 60 ) , 'FM00') || ':' ||
    to_char( mod( abs(date2 - date1) * 24 * 60 * 60, 60) , 'FM00')

I am not sure if FM00 is allowed in Oracle 7.3 and earlier. It prevents Oracle from printing a leading blank when there is no minus sign. If you can't use it, try lpad( to_char( ..), 2, '0') instead.

Arjan. Received on Tue Jul 21 1998 - 15:12:04 CDT