Home » SQL & PL/SQL » SQL & PL/SQL » How I add 2 hours to some time variable?
How I add 2 hours to some time variable? [message #324575] Tue, 03 June 2008 03:35 Go to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
Hi,

How I add 2 hours to some time variable?

Example:

<Time Created> = 03:31

I want to see 05:31 (two hours forward).
Re: How I add 2 hours to some time variable? [message #324576 is a reply to message #324575] Tue, 03 June 2008 03:38 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Add 2/24th of a day to the variable.
Re: How I add 2 hours to some time variable? [message #324707 is a reply to message #324575] Tue, 03 June 2008 13:58 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
just to make the above clearer.

trunc(sysdate) = midnight 12:00 am

trunc(sysdate) + (2/24) = 2am

trunc(sysdate) + (5/48) = 2:30am

[Updated on: Tue, 03 June 2008 13:58]

Report message to a moderator

Re: How I add 2 hours to some time variable? [message #325134 is a reply to message #324707] Thu, 05 June 2008 02:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can also use the Interval notation.
declare
  add_hours interval day to second;
  my_Date   date := sysdate;
begin
  add_hours := numtodsinterval(2,'HOUR');
  
  my_date := my_date + add_hours;

  raise_application_error(-20001,to_char(my_date,'dd-mm-yyyy hh24:mi:ss'));
end;
/

I can't, to be honest, see the advantage of this notation, but it is a valid alternative.
Re: How I add 2 hours to some time variable? [message #325175 is a reply to message #325134] Thu, 05 June 2008 04:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can also:
SQL> select sysdate, sysdate+interval '2' hour from dual;
SYSDATE             SYSDATE+INTERVAL'2'
------------------- -------------------
05/06/2008 11:28:33 05/06/2008 13:28:33

1 row selected.

Regards
Michel
Re: How I add 2 hours to some time variable? [message #325197 is a reply to message #325175] Thu, 05 June 2008 05:04 Go to previous messageGo to next message
star_guan2008
Messages: 4
Registered: June 2008
Junior Member
try
select sysdate,sysdate+2/24 from dual;
Re: How I add 2 hours to some time variable? [message #325201 is a reply to message #325197] Thu, 05 June 2008 05:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This was the first answer, please read the whole topic before posting.

Regards
Michel
Re: How I add 2 hours to some time variable? [message #325281 is a reply to message #325175] Thu, 05 June 2008 09:21 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That inline syntax makes it quite a bit less cumbersome actually.

Thanks for that.
Previous Topic: get error through FORMAT_ERROR_BACKTRACE
Next Topic: pls correct this and it is not completed successfully (merged)
Goto Forum:
  


Current Time: Thu Dec 08 16:04:35 CST 2016

Total time taken to generate the page: 0.28486 seconds