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 query

Posted on 2012-03-30
15
322 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
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: 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
 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle Pivot Question 8 71
oracle- set role and grant privileges 6 38
Oracle - SQL Query with Function 3 53
PL SQL Developer 7 53
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

860 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