Topic: SQL*Plus and PL/SQL >> 24 hours time
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| shake |
Posted: Sep 04, 2008 07:12:09 AM |
Total Post: 85
Joined: Jun, 2008
|
I want the time format in 24 hours. Could anyone help me?.
SELECT SUBSTR((TO_TIMESTAMP(t.actual_workinghours,'hh24:mi:ss') + INTERVAL '04:30' HOUR TO MINUTE),11,8) FROM t_emp_actualworkinghours t;
Here, actual_workinghours is of 'varchar2(8)'.
Here whenever I add a time interval, the result should be produced with 24 hour format. But I'm not getting it.
For instance, if my actual_workinghours is '11.24.33' (hh.mi.ss), after adding '04:30' (hh:mi), it should be '15.54.33'. But I get the answer as '03.54.33'.
|
|
|
 vino |
| Posted: Sep 04, 2008 07:24:12 AM |  |
|
Total Post: 357
Joined: Aug, 2008
|
Hi Shake,
Instead of To_Timestamp try using to_char..
Hope this helps..
|
|
|
|
|
| shake |
| Posted: Sep 04, 2008 07:31:42 AM | |
|
Total Post: 85
Joined: Jun, 2008
|
Yes. You are RIGHT!. Thank You Vino! You are doing a great job. You provide Absolute Answers ans Suggestions.
It works if I use TO_CHAR(). The corrected query follows.
SELECT TO_CHAR(to_timestamp(t.actual_workinghours, 'hh24:mi:ss') + INTERVAL '4:30' HOUR TO minute ,'hh24:mi:ss')
FROM t_emp_actualworkinghours t;
|
|
|
|
|
| vino |
| Posted: Sep 04, 2008 07:53:36 AM | |
|
Total Post: 357
Joined: Aug, 2008
|
Cheers Shake..
Would like to know if you have any queries.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Vinoth Kumar | 357 |
| Gopu Gopi | 340 |
| Gitesh Trivedi | 322 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
