oracle query

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
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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
anumosesAuthor Commented:
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
johnsoneSenior Oracle DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
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
expert9Commented:
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
slightwv (䄆 Netminder) Commented:
>>select to_char(start_time,'HH24:MI:SS')

expert9,
This will generate an error.  start_time is a number not a date.
0
expert9Commented:
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
slightwv (䄆 Netminder) Commented:
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
anumosesAuthor Commented:
Johnsone,

Can I have 8.25 for total hours?
0
slightwv (䄆 Netminder) Commented:
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
expert9Commented:
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
slightwv (䄆 Netminder) Commented:
>>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
expert9Commented:
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
slightwv (䄆 Netminder) Commented:
>>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
anumosesAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.