Home » SQL & PL/SQL » SQL & PL/SQL » adding 1 hour to a time field in PL/SQL
adding 1 hour to a time field in PL/SQL [message #244282] Tue, 12 June 2007 04:26 Go to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
I have a time field which contains the hour in 24 hour format. I need to add 1 hour to this field in PL/SQL. I don't want to use
SQL query to do this. Is there a function in PL/SQL to do this?

example

23 will be 00
01 will be 02
09 will be 10
Re: adding 1 hour to a time field in PL/SQL [message #244283 is a reply to message #244282] Tue, 12 June 2007 04:29 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
What do you call "a time field"? A date, a number, a UDT (user-defined type)? Time is not a valid Oracle datatype.

If you don't want a query, why not use a CASE construction?

MHE
Re: adding 1 hour to a time field in PL/SQL [message #244288 is a reply to message #244283] Tue, 12 June 2007 04:43 Go to previous messageGo to next message
haiza
Messages: 22
Registered: June 2007
Junior Member
The time field is a number. How do you use a CASE constructor in this context?
Re: adding 1 hour to a time field in PL/SQL [message #244290 is a reply to message #244288] Tue, 12 June 2007 04:46 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Something like:
   v_new_hour    := CASE v_old_hour
                      WHEN 23
                         THEN 0
                      ELSE v_old_hour + 1
                   END;


MHE
Re: adding 1 hour to a time field in PL/SQL [message #244295 is a reply to message #244282] Tue, 12 June 2007 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
mod(hour+1,24)

Regards
Michel
Re: adding 1 hour to a time field in PL/SQL [message #244297 is a reply to message #244295] Tue, 12 June 2007 05:11 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Good one, I didn't think of that.

MHE
Re: adding 1 hour to a time field in PL/SQL [message #244382 is a reply to message #244282] Tue, 12 June 2007 09:14 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Why would you store time as a number, this is what a date or timestamp column is for.
Previous Topic: Fetch & Reverse Entry
Next Topic: FIXED ORA-02180 when Creating a Temporary Tablespace
Goto Forum:
  


Current Time: Sat Dec 10 01:00:53 CST 2016

Total time taken to generate the page: 0.08906 seconds