oracle sql conversion ot time

anumoses
anumoses used Ask the Experts™
on
I have a query

select   SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),3,2) as start_time,
           SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),3,2) as end_time,         
        to_char(((to_date(to_char(ds.end_time,'0000')||ds.end_ampm || 'M' ,'HHMIAM')
       -to_date(to_char(ds.start_time,'0000')||ds.start_ampm || 'M' ,'HHMIAM'))*24),'fm99.90') as time_worked
  from department d,emp_unexcld e,dept_staff ds
 where d.department_id = e.department_id
   and e.payroll_id = ds.payroll_id
   and ds.payroll_id = 'ZZW0023516'
   and ds.schedule_date = '02-sep-2011'
   and ds.site_code = 'N208'
----------------------------------------------
START_TIME|END_TIME|TIME_WORKED
08:00          |04:30       |8.50
--------------------------------
I want to insert this data in the table
Table has
START_TIME                    NUMBER(9),
END_TIME                    NUMBER(9),
TOTAL_HOURS                    NUMBER(9)
------------------------
I want to insert start_time  as 800
end time as 430
and total_hours(time_worked) as 850.
Help appreciated
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
awking00Information Technology Specialist
Commented:
to_number(replace(start_time,':'))
to_number(replace(end_time,':'))
to_number(replace(total_hours,'.'))
Information Technology Specialist
Commented:
For the insert, see attached.
query.txt
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
No need to replace the ':'.  Just remove it form your query

change:
select   SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),3,2) as start_time,
...


to:
select   SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),1,2) || SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),3,2) as start_time,
...

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
thanks

Author

Commented:
Have one question
The payroll_id is already there in dept_staff table.
So I have to update the table
So can I say
update dept_staff
select.................

Author

Commented:
update dept_staff
set(start_time,end_time,total_hours= (
select   TO_NUMBER(REPLACE(SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),3,2),':')) as start_time,
         TO_NUMBER(REPLACE(SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),3,2),':')) as end_time,          
         TO_NUMBER(REPLACE(to_char(((to_date(to_char(ds.end_time,'0000')||ds.end_ampm || 'M' ,'HHMIAM')
         -to_date(to_char(ds.start_time,'0000')||ds.start_ampm || 'M' ,'HHMIAM'))*24),'fm99.90'),'.')) as time_worked
from department d,emp_unexcld e,dept_staff ds
where d.department_id = e.department_id
  and e.payroll_id = ds.payroll_id
  and ds.payroll_id = 'ZZW0023516'
  and ds.schedule_date = '02-sep-2011'
  and ds.site_code = 'N208');
----------------------------

Is this correct?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>Is this correct?

No.

There is no where clause on the update itself.  This will update EVERY row.

This kind of update is a little tricky.  I suggest you use the MERGE SQL instead.

Author

Commented:
I tried to wrote this. Ccn ayou tell me if this is correct?

merge into dept_staff a
using (
select   TO_NUMBER(REPLACE(SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.start_time, 'FM0000'),3,2),':')) as start_time,
         TO_NUMBER(REPLACE(SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),1,2) || ':' || SUBSTR(TO_CHAR(ds.end_time, 'FM0000'),3,2),':')) as end_time,          
         TO_NUMBER(REPLACE(to_char(((to_date(to_char(ds.end_time,'0000')||ds.end_ampm || 'M' ,'HHMIAM')
         -to_date(to_char(ds.start_time,'0000')||ds.start_ampm || 'M' ,'HHMIAM'))*24),'fm99.90'),'.')) as time_worked
from department d,emp_unexcld e,dept_staff ds
where d.department_id = e.department_id
  and e.payroll_id = ds.payroll_id
  and ds.payroll_id = 'ZZW0023516'
  and ds.schedule_date = '02-sep-2011'
  and ds.site_code = 'N208') b
  ON (a.payroll_id = b.payroll_id)
  WHEN MATCHED THEN
  update dept_staff
  set (a.start_time,a.end_time,a.total_hours)=
  (b.start_time,b.end_time,b.time_worked)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>Ccn ayou tell me if this is correct?

Not really.  The syntax looks OK but that is all I can comment on since I don't have your tables/data to actually test it.

Does is work?  If so, then it is correct.

Author

Commented:
I will add another question with the query and will give the data that is already there.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial