Solved

oracle sql conversion ot time

Posted on 2011-09-07
10
532 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 77

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
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 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
 
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 77

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 77

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

820 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