Solved

oracle query

Posted on 2012-03-30
15
323 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
[X]
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
  • 7
  • 4
  • 3
  • +1
15 Comments
 
LVL 77

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 35

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

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 77

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

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 77

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 77

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 77

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

735 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