• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 716
  • Last Modified:

Oracle SQL Question

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
anumoses
Asked:
anumoses
  • 9
  • 8
3 Solutions
 
slightwv (䄆 Netminder) Commented:
Something like:

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

06:30 AM
0
 
sdstuberCommented:


0630  = 6:30

but

1050  = 10:30   ?
0
 
sdstuberCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
anumosesAuthor Commented:
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
 
sdstuberCommented:
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
 
anumosesAuthor Commented:
: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
 
sdstuberCommented:
nulls?

are you sure your variables are populated with what you think they should be?
if so, how have you verified that assumption?
0
 
anumosesAuthor Commented:
I put messages to populate start time and time worked

start time is 06:30
time worked 10.5
0
 
sdstuberCommented:
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
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
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
 
sdstuberCommented:
>>> 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
 
anumosesAuthor Commented:
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
 
anumosesAuthor Commented:
 
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
 
sdstuberCommented:
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
 
anumosesAuthor Commented:
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
 
sdstuberCommented:
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
 
anumosesAuthor Commented:
thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now