Solved

oracle sql conversion ot time

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

Suggested Solutions

Title # Comments Views Activity
EXECUTE IMMEDIATE 5 69
Why  don't I see Below columns in my  Stored Procedure  under TMP_RS? 8 22
Oracle DB monitor SW 21 48
Oracle collections 15 23
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

770 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