Solved

oracle sql conversion ot time

Posted on 2011-09-07
10
525 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:anumoses
  • 5
  • 3
  • 2
10 Comments
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 167 total points
ID: 36496133
to_number(replace(start_time,':'))
to_number(replace(end_time,':'))
to_number(replace(total_hours,'.'))
0
 
LVL 31

Accepted Solution

by:
awking00 earned 167 total points
ID: 36496181
For the insert, see attached.
query.txt
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 83 total points
ID: 36496206
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,
...

0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 36496302
thanks
0
 
LVL 6

Author Comment

by:anumoses
ID: 36496549
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.................
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:anumoses
ID: 36496624
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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36496784
>>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.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36496857
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)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36496883
>>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.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36496927
I will add another question with the query and will give the data that is already there.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now