Oracle date difference | date difference in sql and PL/SQL

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

Oracle maintains the SYSDATE pseudo-column to show the current system date and time.
How to find the difference between two dates in Oracle? The following examples gives different useful methods to do this.

With Oracle Dates, this is simple by subtracting the dates and do some minor modification to get get Days/Hours/Minutes/Seconds between the dates.

See the following example.

SQL> set serveroutput on;
SQL> declare
   today       date;
   nextday       date;
begin
  today := sysdate;<
   --Add 1 day to sysdate
   nextday := sysdate+1;
   dbms_output.put_line('>> Tomorrow is ' || (nextday-today) || ' day ahead of today' );
   dbms_output.put_line('>> Tomorrow is ' || (nextday-today)*24 || ' hours ahead of today ' );
   dbms_output.put_line('>> Tomorrow is ' || (nextday-today)*24*60 || '  minutes ahead of today' );
   dbms_output.put_line('>> Tomorrow is ' || (nextday-today)*24*60*60 || ' seconds ahead of today');
  end;
 /

 >> Tomorrow is 1 day ahead of today
 >> Tomorrow is 24 hours ahead of today
 >> Tomorrow is 1440  minutes ahead of today
 >> Tomorrow is 86400 seconds ahead of today</span>

PL/SQL procedure successfully completed.

Adding 1 to SYSDATE will advance the date by 1 day. By adding fractions to sysdate we can advance by hours,minutes and seconds.

Current time :SYSDATE
Tomorrow :SYSDATE+1
One hour from now :SYSDATE + 1/24
One minute from now :SYSDATE + 1/1440
Five second from now :SYSDATE + 5/86400

See the example below,

SQL> set serveroutput on;
SQL>  declare
  begin
     dbms_output.put_line('>> Current Time        :' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
    dbms_output.put_line('>> Tomorrow is         :' || TO_CHAR((SYSDATE + 1),'DD-MON-YYYY HH24:MI:SS'));
    dbms_output.put_line('>> One hour from now   :' || TO_CHAR((SYSDATE + 1/24),'DD-MON-YYYY HH24:MI:SS'));
    dbms_output.put_line('>> One minute from now :' || TO_CHAR((SYSDATE + 1/1440),'DD-MON-YYYY HH24:MI:SS'));
    dbms_output.put_line('>> Five second from now: ' || TO_CHAR((SYSDATE + 5/86400),'DD-MON-YYYY HH24:MI:SS'));
end;

>> Current Time        :13-NOV-2008 12:45:29
>> Tomorrow is         :14-NOV-2008 12:45:29
>> One hour from now   :13-NOV-2008 13:45:29
>> One minute from now :13-NOV-2008 12:46:29
>> Five second from now: 13-NOV-2008 12:45:34
PL/SQL procedure successfully completed.
SQL>

Related Topics,

DATE Data type in Oracle|Oracle Dates and Times|Dates in Oracle
Formatting date in SQL (Oracle) -Simple date forma…

Also Read,

Technorati Tags:
, , , ,

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

2 thoughts on “Oracle date difference | date difference in sql and PL/SQL

Leave a Reply

Your email address will not be published. Required fields are marked *

Paged comment generated by AJAX Comment Page