[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

oracle query

Posted on 2012-03-30
15
Medium Priority
?
335 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 78

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 1000 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 78

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 78

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 78

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 78

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 78

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 78

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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

611 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