OraFAQ Forum: SQL & PL/SQL » Problem with calculating hours

Home » SQL & PL/SQL » SQL & PL/SQL » Problem with calculating hours (oracle sql 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with calculating hours [message #637798]

Mon, 25 May 2015 07:07

ifti6650
Messages: 10
Registered: March 2015
Location: DHAKA

Junior Member

hi this is my query and i have been facing problem with calculating late hous

select emp_id,IN_TIME, OFFICE_TIME,  24/(to_date(OFFICE_TIME, 'DD/MM/RRRR HH:MI:SS AM')
             - to_date(IN_TIME, 'YYYY-MM-DD hh24:mi')) late_hours 
       from EMP_ATTENDANCE
       where officedate = to_date(sysdate,'dd/mm/rrrr')

EMP_ID	        IN_TIME	                OFFICE_TIME	        LATE_HOURS

SMAC-0010	5/25/2015 9:39:52 AM	5/25/2015 10:20:00 AM	3.30194649746025E-5
SMAC-0038	5/25/2015 9:42:46 AM	5/25/2015 10:20:00 AM	3.30194649746025E-5
SMAC-0011	5/1/2015 9:30:00 AM	5/25/2015 10:20:00 AM	3.26259838773263E-5
SMAC-0015	5/1/2015 9:50:00 AM	5/25/2015 10:20:00 AM	3.26259838773263E-5
SMAC-0013	5/25/2015 10:09:30 AM	5/25/2015 10:20:00 AM	3.30194649746025E-5
SMAC-0045	5/25/2015 10:11:11 AM	5/25/2015 10:20:00 AM	3.30194649746025E-5

the late hourse should look like 15 min

please help me in this reagards

ifti

Lalit : Added code tags, and fixed formatting of the output

Attachment: Capture.JPG
(Size: 45.80KB, Downloaded 799 times)

[Updated on: Mon, 25 May 2015 07:11] by Moderator

Report message to a moderator

Re: Problem with calculating hours [message #637799 is a reply to message #637798]

Mon, 25 May 2015 07:13

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Why are you applying to_date function to data types that are already dates? This is a recipie for incorrect results before you even consider any issues with your formula.

Report message to a moderator

Re: Problem with calculating hours [message #637800 is a reply to message #637799]

Mon, 25 May 2015 07:20

ifti6650
Messages: 10
Registered: March 2015
Location: DHAKA

Junior Member

TO_DATE FUNCTION IS USING FORMATING DATE ('DD/MM/RRRR HH:MI:SS AM'). I WANT TO CALCULATE (OFFICE_TIME - IN_TIME) RESULTING DATE INTO HOURS/MINUTS.

THANKS

Report message to a moderator

Re: Problem with calculating hours [message #637801 is a reply to message #637800]

Mon, 25 May 2015 07:29

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

date-date results in the answer in days.

SQL> select trunc(sysdate) - trunc(sysdate-1) from dual;

TRUNC(SYSDATE)-TRUNC(SYSDATE-1)
-------------------------------
                              1

Just apply the required math to get minutes.

and "to_date" isn't a format mask. Remove it if the data types are date.

Report message to a moderator

Re: Problem with calculating hours [message #637802 is a reply to message #637800]

Mon, 25 May 2015 07:32

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

>to_date(sysdate,'dd/mm/rrrr')

A date doesn't have any format. What you see is for display so that you could interpret the format easily. While doing date arithmetic, use the date as it is. Use TO_DATE only to convert a string into date, and TO_CHAR to display the date in your desired format.

The difference between two dates will result in a number which is the difference in days.

And using ALL CAPS is considered shouting on forums.

Report message to a moderator

Re: Problem with calculating hours [message #637804 is a reply to message #637802]

Mon, 25 May 2015 09:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member


  1* select (trunc(sysdate) - trunc(sysdate-1)) days, (trunc(sysdate) - trunc(sysdate-1)) * 24 hours, (trunc(sysdate) - trunc(sysdate-1)) *24*60 minutes from dual
SQL> /

      DAYS      HOURS    MINUTES
---------- ---------- ----------
         1         24       1440

Report message to a moderator

Re: Problem with calculating hours [message #637805 is a reply to message #637798]

Mon, 25 May 2015 09:19

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Problem with calculating hours [message #637806 is a reply to message #637798]

Mon, 25 May 2015 09:23

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Sun, 12 April 2015 08:30

1/ Do not post in UPPER case this is read as SHOUTING.

2/ Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

3/ Also always post your Oracle version, with 4 decimals.

4/ With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...

7/ Quote:
TO_DATE(E.VOUCHER_DATE,'DD/MM/RRRR')

If E.VOUCHER_DATE is of DATE datatype, TO_DATE on a date is silly
...

ifti6650 wrote on Wed, 15 April 2015 10:35

data type is date, I wanted to use to_date function in column ''VOUCHER_DATE'' for formatting date. That was my objective. Finally i used mydate <= :bindvardate. and I solve it. Thanks a lot !

Michel Cadot wrote on Wed, 15 April 2015 11:07

You do not use TO_DATE to format a date (to display), you use TO_CHAR.
TO_DATE is to convert a string to a date datatype.

Lalit Kumar B wrote on Wed, 15 April 2015 12:21

ifti6650 wrote on Wed, 15 April 2015 14:05
data type is date, I wanted to use to_date function in column ''VOUCHER_DATE'' for formatting date. That was my objective.

Never, ever use TO_DATE on a DATE. Oracle will do an implicit conversion. First, from date into string. And then, from string into date using locale-specific nls_date_format.

For example,
...

And feedback and thank people who spend time to help you.

Report message to a moderator