Solved

oracle sql conversion ot time

Posted on 2011-09-07
10
526 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 32

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 32

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

867 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

23 Experts available now in Live!

Get 1:1 Help Now