Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle SQL Question

Posted on 2011-09-02
18
701 Views
Last Modified: 2012-06-27
start time

select SUBSTR(TO_CHAR(0630, 'FM0000'),1,2)
   || ':' || SUBSTR(TO_CHAR(0630, 'FM0000'),3,2) as start_time from dual
   
worked time

select 1050 / 100 from dual;  

end time to derive

required result 5:00 (PM)
-------------------
The user inputs start time 0630(gets converted to 06:30)
and time worked as 1050( gets converted to 10.50)
Now the required time is 5:00 PM. Help is appreciated.
screen.JPG
0
Comment
Question by:anumoses
  • 9
  • 8
18 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36473169
Something like:

SQL> select to_char(to_date('0630','HH24MI'),'HH:MI AM') from dual;

06:30 AM
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36473219


0630  = 6:30

but

1050  = 10:30   ?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36473229
select to_char(to_date(SUBSTR(TO_CHAR(0630, 'FM0000'),1,2)
   || ':' || SUBSTR(TO_CHAR(0630, 'FM0000'),3,2),'hh24:mi') + (1050/100/24),'hh12:miPM') from dual
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: 36473386
select to_char(to_date(SUBSTR(TO_CHAR(0630, 'FM0000'),1,2)
   || ':' || SUBSTR(TO_CHAR(0630, 'FM0000'),3,2),'hh24:mi') + (1050/100/24),'hh12:miPM') from dual

This is a perfect solution. Can we exclude PM?
and get only 05:00 populated?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 36473390
sure, just remove the PM

elect to_char(to_date(SUBSTR(TO_CHAR(0630, 'FM0000'),1,2)
   || ':' || SUBSTR(TO_CHAR(0630, 'FM0000'),3,2),'hh24:mi') + (1050/100/24),'hh12:mi') from dual
0
 
LVL 6

Author Comment

by:anumoses
ID: 36473893
:dtl.end_time := to_char(to_date(SUBSTR(TO_CHAR(:dtl.start_time, 'FM0000'),1,2)
         || ':' || SUBSTR(TO_CHAR(:dtl.start_time, 'FM0000'),3,2),'hh24:mi') + (:dtl.time_worked/100/24),'hh12:mi');

               message('end time1 ='||:dtl.end_time);pause;

When I run the query in toad replace the block values with the above values I get the result as 05:00. In forms I put message, but the value is not populated. Any form experts as to why?
field end_time char(6)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36473981
nulls?

are you sure your variables are populated with what you think they should be?
if so, how have you verified that assumption?
0
 
LVL 6

Author Comment

by:anumoses
ID: 36474247
I put messages to populate start time and time worked

start time is 06:30
time worked 10.5
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36474269
what is :dtl.start_time  ?
based on the rest of the question it should be 0630 not 06:30


what is :dtl.time_worked   ?
based on the rest of the question it should be  1050 not 10.5  
0
 
LVL 6

Author Comment

by:anumoses
ID: 36474372
In the above can I seperate the start time and time worked?
I tried but I get inconsistent datatypes error
select to_char(to_date(SUBSTR(TO_CHAR(0630, 'FM0000'),1,2)
         || ':' || SUBSTR(TO_CHAR(0630, 'FM0000'),3,2),'hh24:mi') ,
             (1050/100/24),'hh12:mi') from dual
0
 
LVL 6

Author Comment

by:anumoses
ID: 36474404
we ae formatting the fileds from 0630 to 06:30 and 1050 to 10.50
so do we have to reformat back to 0630 and 1050 in order to get 0500?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36474426
>>> so do we have to reformat back to 0630 and 1050 in order to get 0500?

yes, of course.

the function calls above are based on the input you specfied,  which as 0630 and 1050.

if you don't want to use those inputs then the function calls will be different
0
 
LVL 6

Author Comment

by:anumoses
ID: 36474438
once I put put 0630 becomes 06:30
and 1050 becomes 10.50
so what is the query to convert them back to 0630 and 1050? Would appreciate.
0
 
LVL 6

Author Comment

by:anumoses
ID: 36474479
 
select to_char(to_date(SUBSTR(TO_CHAR(0630, 'FM0000'),1,2)||
SUBSTR(TO_CHAR(0630, 'FM0000'),3,2),'hh24mi'),'hh12mi') from dual
------
0630
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 36474514
if the input you have is 06:30 and 10.5 that's fine
it actually makes it easier


 TO_CHAR(TO_DATE(:start_time, 'hh24:mi') + (:time_worked / 24), 'hh:mi')
0
 
LVL 6

Author Comment

by:anumoses
ID: 36474561
one last question
I know we removed PM.
but is there a way to add that as a column to make sure the time is AM or PM?
for eg if the employee starts at 0630 and ends the shift at 1130, its 11:30AM
So if I can get it as a seperate column in the query I would appreciate it.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 36474607
TO_CHAR(TO_DATE(:start_time, 'hh24:mi') + (:time_worked / 24), 'hh:mi')   time


TO_CHAR(TO_DATE(:start_time, 'hh24:mi') + (:time_worked / 24), 'PM')   am/pm
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 36475169
thanks
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
Query to identify changes between rows of two tables 8 55
Checking for column width 8 29
update using pipeline function 3 21
return value in based on value passed 6 28
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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

838 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