| Time Calculation [message #527757] |
Thu, 20 October 2011 02:00  |
snsiddiqui
Messages: 172
Registered: December 2008
|
Senior Member |
|
|
Dear All,
Currently I am working on payroll system where I have to calculate employees working hours/late coming hours/early going hours against its roster which is defined in the beginning of every month/week.
In roster form user define shift of every employee like
Code Name Shift
7 Saad Nafees A - 09:00 17:00
492 Muhammad Nasir Shahzad B - 17:00 01:00
243 Muhammad Tahir C - 01:00 09:00
Roster table structure
code varchar2
name varchar2
Shift date
Remarks varchar2
shift table structure
code varchar2
timein date
timeout date
latetime date
Hdaytime date
Oracle stores both date and time information in date data type, suppose today user change shift timings from 17:00 to 17:30 or user change timein/timeout in attendance form then oracle will store current date with user define timings.
Now this is the main problem which I am facing because whenever you calculate difference between timein and timeout or compare with its roster then output comes wrong because oracle returns total no of hours whenever you minus two dates.
I will be very grateful to all of you, if you will help me in this project.
|
|
|
|
|
|
|
|
|
|
| Re: Time Calculation [message #527970 is a reply to message #527965] |
Fri, 21 October 2011 01:59  |
snsiddiqui
Messages: 172
Registered: December 2008
|
Senior Member |
|
|
Yes, you are right I am subtracting TIME OUT - TIME IN therefore I am getting correct result, you can also check in image.
Yes, it is OCT 1st.
Actually, I want to know that if today I change TIME IN then oracle should save today's date with time but oracle is saving this month's starting date therefore I am unable to get correct result.
|
|
|
|
| Re: Time Calculation [message #527973 is a reply to message #527970] |
Fri, 21 October 2011 02:11 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Quote:if today I change TIME IN ...
That depends on what you EXACTLY do. By default, if you don't specify DATE component, it is truncated to the first of current month:
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi';
Session altered.
SQL> select to_date('09:05', 'hh24:mi') from dual;
TO_DATE('09:05',
----------------
01.10.2011 09:05
SQL>
Image you attached contains 4 items: date_in, time_in, date_out, time_out. What do you have in a table? How many columns? 4 as well, or just two (date_in, date_out)? I guess that your life would be easier if it were only two columns. Otherwise, you'll have to check what's written in DATE_IN when saving TIME_IN and update the value accordingly to their combination.
|
|
|
|
|
|
|
|
| Re: Time Calculation [message #528027 is a reply to message #528002] |
Fri, 21 October 2011 05:20 |
snsiddiqui
Messages: 172
Registered: December 2008
|
Senior Member |
|
|
I applied your given suggestion but it is giving me error;
ORA-01861: literal does not match format string
:emp_attnd_mast.eam_atime := to_date(to_char(:emp_attnd_mast.eam_date,'DDMMYYYY')||to_char(:emp_attnd_mast.eam_atime,'HH24:MI')); |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
