Solved

oracle query

Posted on 2012-03-30
15
317 Views
Last Modified: 2012-03-30
select start_time,end_time,total_hours
from dept_staff
where PAYROLL_ID = 'ZZW0024277'
AND SCHEDULE_DATE = '01-apr-2012'

START_TIME|END_TIME|TOTAL_HOURS
   545                200                825

I want the input as 5:45  2:00 and 8.25
0
Comment
Question by:anumoses
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37788450
Is start_time and end_time a number?
Do you calculate it?

Without AM and PM on the times how do you know total_hours?
0
 
LVL 6

Author Comment

by:anumoses
ID: 37788468
select start_time,start_ampm,end_time,end_ampm,total_hours
from dept_staff
where PAYROLL_ID = 'ZZW0024277'
AND SCHEDULE_DATE = '01-apr-2012'

START_TIME|START_AMPM|END_TIME|END_AMPM|TOTAL_HOURS
     545|              A|              200|              P|            825

start time, end time and total hours are numbers in the database
0
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
ID: 37788512
Try this:

select replace(to_char(start_time/100, 'fm00.00'), '.', ':') start_time,
start_ampm,
replace(to_char(end_time/100, 'fm00.00'), '.', ':') end_time,
end_ampm,total_hours
from dept_staff
where PAYROLL_ID = 'ZZW0024277'
AND SCHEDULE_DATE = '01-apr-2012';

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37788533
johnsone should be correct.

I mis-read the question.  I was thinking you wanted to calculate total hours not just format what you had.
0
 

Expert Comment

by:expert9
ID: 37788574
please use the following query :-

if you want the hours in 24 run this

select to_char(start_time,'HH24:MI:SS') as start_time, to_char(end_time,'HH24:MI:SS') as end_time, total_hours from dept_staff
where PAYROLL_ID = 'ZZW0024277'
AND SCHEDULE_DATE = '01-apr-2012'

if you want the hours in 12 then run this

select to_char(start_time,'HH:MI:SS') as start_time, to_char(end_time,'HH24:MI:SS') as end_time, total_hours from dept_staff
where PAYROLL_ID = 'ZZW0024277'
AND SCHEDULE_DATE = '01-apr-2012'

OR

select to_char(start_time,'HH12:MI:SS') as start_time, to_char(end_time,'HH24:MI:SS') as end_time, total_hours from dept_staff
where PAYROLL_ID = 'ZZW0024277'
AND SCHEDULE_DATE = '01-apr-2012'
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37788584
>>select to_char(start_time,'HH24:MI:SS')

expert9,
This will generate an error.  start_time is a number not a date.
0
 

Expert Comment

by:expert9
ID: 37788610
ok then do this

select to_char(to_char(start_time,'0000'),'HH24:MI:SS')

this will add 0 if they will not be 4 in length :)

try and tell me
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 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37788630
expert9,

They just want to format the number not convert to date then back to string.  Expert johnsone has provided what I believe to be the answer.

>>try and tell me

Please try things yourself before posting.  That also errors:

select to_char(to_char(123,'0000'),'HH24:MI:SS') from dual;
0
 
LVL 6

Author Comment

by:anumoses
ID: 37788682
Johnsone,

Can I have 8.25 for total hours?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37788692
johnsone did all the work and should get all the points.

>>Can I have 8.25 for total hours?

Would that not be the same to_char used for times without the replace?
0
 

Expert Comment

by:expert9
ID: 37788707
sorry the to_Char in above should be to_Date i copied wrong while paste from my local oracle server by the way if its for display purpose you can use this , i thought it would be for display and could be converted to date just the HHMM in date no other....

SELECT TO_DATE(TO_CHAR(825,'0000'),'HH24MI') FROM DUAL;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37788727
>>SELECT TO_DATE(TO_CHAR(825,'0000'),'HH24MI') FROM DUAL;

Although that might run, it does nothing to answer the question.

They want to take a number: 123 and format it like: 1:23
Then for total hours, take a number: 825 and format it like 8.45
0
 

Expert Comment

by:expert9
ID: 37788753
SELECT TO_DATE(TO_CHAR(825,'0000'),'HH24:MI:SS') FROM DUAL;

u get it formatted.... the 8.25 can be get with the help of decimal precision format
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37788771
>>the 8.25 can be get with the help of decimal precision format

And that is what johnsone posted in http:#a37788512

Converting it into a date first is unecessary.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 37789236
thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

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

22 Experts available now in Live!

Get 1:1 Help Now