Home » SQL & PL/SQL » SQL & PL/SQL » rows between 2 times (Oracle 9i windows)
rows between 2 times [message #432895] Fri, 27 November 2009 02:18 Go to next message
namb
Messages: 35
Registered: September 2009
Member
I want to find the employees who entered between 8 am and 5 pm

The fields are and the data is stored as
in_time (date) = 14/Nov/2009 00:08:00 AM
out_Time (date)= 14/Nov/2009 00:05:00 PM

Can someone please help to find the employees who entered between these times.

How about if to find between the times 10 am to 5 pm.

Please help
Re: rows between 2 times [message #432896 is a reply to message #432895] Fri, 27 November 2009 02:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What's wrong with
date between 
  to_date('14/Nov/2009 08:00:00 AM','dd/mon/yyyy hh24:mi:ss') 
and 
  to_date('14/Nov/2009 17:00:00 AM','dd/mon/yyyy hh24:mi:ss') 
?

[MC: Adding second to_date that was missing]

[Updated on: Fri, 27 November 2009 02:49] by Moderator

Report message to a moderator

Re: rows between 2 times [message #432899 is a reply to message #432896] Fri, 27 November 2009 02:50 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Or, if you want to have it more generic, use date arithmetic.
If you want to check for a certain date, you can use
where in_time between to_date('<your_date_as_dd-mm-yyyy'>, 'dd-mm-yyyy') + 5/24 and to_date('<your_date_as_dd-mm-yyyy'>, 'dd-mm-yyyy') + 8/24
Re: rows between 2 times [message #432900 is a reply to message #432896] Fri, 27 November 2009 02:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"HH24" and "AM" are exclusive.
Format time part should be "HH:MI:SS AM".

Regards
Michel
Re: rows between 2 times [message #432902 is a reply to message #432900] Fri, 27 November 2009 03:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I know that.
I could have sworn I trimmed the AMs off when I used a HH24 mask and converted the OPs date format to change the times from 00:08:00 to 08:00:00.

Not enought sleep, I guess.
Re: rows between 2 times [message #432903 is a reply to message #432896] Fri, 27 November 2009 03:15 Go to previous messageGo to next message
namb
Messages: 35
Registered: September 2009
Member
But what if the date is not fixed here, if i want to use this statement in generalized way mean to say if instead of 14-nov-2009 i want to use in_time field here what the procedure

Many thanks
Re: rows between 2 times [message #432904 is a reply to message #432903] Fri, 27 November 2009 03:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I answered that.

And if you want to use the date in the table, use
where in_time between trunc(in_time + 5/24) and trunc(in_time + 8/24)

Read up on date-arithmetic functions.

[Updated on: Fri, 27 November 2009 03:19]

Report message to a moderator

Re: rows between 2 times [message #432906 is a reply to message #432903] Fri, 27 November 2009 03:18 Go to previous messageGo to next message
namb
Messages: 35
Registered: September 2009
Member
I mean to say can i use in_time instead of specific date here because date can be any but the time has to be fixed
Re: rows between 2 times [message #432907 is a reply to message #432906] Fri, 27 November 2009 03:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
namb wrote on Fri, 27 November 2009 10:18
I mean to say can i use in_time instead of specific date here because date can be any but the time has to be fixed

Just edited my post to add this in.
Re: rows between 2 times [message #432908 is a reply to message #432907] Fri, 27 November 2009 03:29 Go to previous messageGo to next message
namb
Messages: 35
Registered: September 2009
Member
can i use here



to_date('in_time 08:00:00 AM','dd/mon/yyyy hh24:mi:ss')



as i want date time to be hardcoded on daily basis and date to be picked from in_time
Re: rows between 2 times [message #432909 is a reply to message #432908] Fri, 27 November 2009 03:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why do you want to make up syntax, instead of using what I suggested?
Re: rows between 2 times [message #432910 is a reply to message #432909] Fri, 27 November 2009 03:40 Go to previous messageGo to next message
namb
Messages: 35
Registered: September 2009
Member
Frank thanks a lot for being patiently replying

ITs not like that actually the problem is that i want only the time field out of the intime because i have to use that in the if loop of my procedure so i was looking for the general syntax for that. Also when i used your formula it only gives me the date.

Many thanks
Re: rows between 2 times [message #432911 is a reply to message #432910] Fri, 27 November 2009 03:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Show us what query you used, what the outcome was and what outcome you expected. I don't understand your current problem
Re: rows between 2 times [message #432914 is a reply to message #432911] Fri, 27 November 2009 04:18 Go to previous messageGo to next message
namb
Messages: 35
Registered: September 2009
Member


CREATE or REPLACE TRIGGER emp_det_tgr
AFTER INSERT OR UPDATE
    ON musterdata
REFERENCING NEW AS NEW OLD AS OLD    
FOR EACH ROW
BEGIN
if inserting or updating then

	
[b]if :new.in_time [/b]



	insert into atten_det values ('1',:new.eid,null,null,null,null,:new.musterdate,'P','P',:new.in_time,:new.out_time,
        :new.vc_shift_code,null,null,null,(to_number(Trim(REPLACE(:new.work_hours,':','.'))))
,null,null,null,null,null,null,
        null,null,null,null,null,null,null,null,null,null,null);
	if :new.vc_shift_code='MG' then
        update atten_det set vc_shift_code = 'A' where vc_emp_code=:new.eid and dt_atten = :new.musterdate;
        elsif :new.vc_shift_code='EG' then
        update atten_det set vc_shift_code = 'B' where vc_emp_code=:new.eid and dt_atten = :new.musterdate;        
	elsif :new.vc_shift_code='NT' then
        update atten_det set vc_shift_code = 'C' where vc_emp_code=:new.eid and dt_atten = :new.musterdate; 
        elsif :new.vc_shift_code='GN' then
        update atten_det set vc_shift_code = 'G' where vc_emp_code=:new.eid and dt_atten = :new.musterdate; 	
	end if;
        
end if;

if (:new.work_hours is NULL ) then

	insert into atten_det values ('1',:new.eid,null,null,null,null,:new.musterdate,'A','A',null,null,
        null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
end if;


if (to_number(Trim(REPLACE(:new.work_hours,':','.'))) < 8 ) then

	insert into atten_det values ('1',:new.eid,null,null,null,null,:new.musterdate,'A','A',:new.in_time,:new.out_time,
        :new.vc_shift_code,null,null,(to_number(Trim(REPLACE(:new.work_hours,':','.')))),(to_number(Trim(REPLACE(:new.work_hours,':','.'))))
,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

        if :new.vc_shift_code='MG' then
        update atten_det set vc_shift_code = 'A' where vc_emp_code=:new.eid and dt_atten = :new.musterdate;
        elsif :new.vc_shift_code='EG' then
        update atten_det set vc_shift_code = 'B' where vc_emp_code=:new.eid and dt_atten = :new.musterdate;        
	elsif :new.vc_shift_code='NT' then
        update atten_det set vc_shift_code = 'C' where vc_emp_code=:new.eid and dt_atten = :new.musterdate; 
	elsif :new.vc_shift_code='GN' then
        update atten_det set vc_shift_code = 'G' where vc_emp_code=:new.eid and dt_atten = :new.musterdate; 	
	end if;
end if;

if (to_number(Trim(REPLACE(:new.work_hours,':','.'))) > 8 ) then

insert into atten_det values ('1',:new.eid,null,null,null,null,:new.musterdate,'P','P',:new.in_time,:new.out_time,
        :new.vc_shift_code,null,null,(to_number(Trim(REPLACE(:new.ot,':','.')))),(to_number(Trim(REPLACE(:new.work_hours,':','.'))))
,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);
        
        if :new.vc_shift_code='MG' then
        update atten_det set vc_shift_code = 'A',vc_ot_shift= 'B' where vc_emp_code=:new.eid and dt_atten = :new.musterdate;
        elsif :new.vc_shift_code='EG' then
        update atten_det set vc_shift_code = 'B',vc_ot_shift= 'C' where vc_emp_code=:new.eid and dt_atten = :new.musterdate;           
elsif :new.vc_shift_code='NT' then
        update atten_det set vc_shift_code = 'C',vc_ot_shift= 'A' where vc_emp_code=:new.eid and dt_atten = :new.musterdate;
        elsif :new.vc_shift_code='GN' then
        update atten_det set vc_shift_code = 'G',vc_ot_shift= 'G' where vc_emp_code=:new.eid and dt_atten = :new.musterdate;          
end if;



end if;


end if;

end emp_det_tgr;

/
show errors;

commit;





On 8th line i need to find intime between 7am to 3 pm

[edited to drop line length]

[Updated on: Fri, 27 November 2009 04:32] by Moderator

Report message to a moderator

Re: rows between 2 times [message #432915 is a reply to message #432914] Fri, 27 November 2009 04:26 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Next time please only send relevant part of the code..

Now why can you not use my last example?
Quote:
where in_time between trunc(in_time + 5/24) and trunc(in_time + 8/24)


Oops... typo in the parentheses..
Should be
where in_time between trunc(in_time) + 5/24 and trunc(in_time) + 8/24


However, if you would have studied my suggestion and would have followed the advice to read some docs on date-arithmetics, you could have spotted it yourself and would have been able to get the idea of my example.

[Updated on: Fri, 27 November 2009 04:32]

Report message to a moderator

Re: rows between 2 times [message #432916 is a reply to message #432914] Fri, 27 November 2009 04:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What data type is IN_TIME?
Re: rows between 2 times [message #432918 is a reply to message #432916] Fri, 27 November 2009 05:13 Go to previous messageGo to next message
namb
Messages: 35
Registered: September 2009
Member
in_time = date type

thanks all
Re: rows between 2 times [message #432919 is a reply to message #432918] Fri, 27 November 2009 05:21 Go to previous messageGo to next message
namb
Messages: 35
Registered: September 2009
Member
Frank

Thanks

Can you elaborate on the code u gave
Re: rows between 2 times [message #432920 is a reply to message #432919] Fri, 27 November 2009 05:34 Go to previous messageGo to next message
namb
Messages: 35
Registered: September 2009
Member
In the code above that i gave i have three shifts

7:00 to 15:00

15:00 to 23:00

23:00 to 7:00 (next day)


in my if statement i want to include these shifts timings so as to update the tables according to the shifts. Please help how to extract the time field of the in_time and out_Time fields

thanks
Re: rows between 2 times [message #432937 is a reply to message #432919] Fri, 27 November 2009 08:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
namb wrote on Fri, 27 November 2009 12:21

Can you elaborate on the code u gave

If you add 1 to a given date in Oracle, you add 1 day to it. So if you want 5 AM on a given day, you would have to add 5/24 to the start of that day (00:00:00).
To get the start of a given date, you can trunk it to the day:
trunc(<a_date>, 'DD'). Since day is the default value for trunk, this can be rewritten as trunk(<a_date>)

So, trunk(<a_date>) + 5/24 means 05:00 (AM that is) on <a_date>
Re: rows between 2 times [message #432989 is a reply to message #432937] Sat, 28 November 2009 03:02 Go to previous messageGo to next message
namb
Messages: 35
Registered: September 2009
Member
In the code above that i gave i have three shifts

7:00 to 15:00

15:00 to 23:00

23:00 to 7:00 (next day)


in my if statement i want to include these shifts timings so as to update the tables according to the shifts. Please help how to extract the time field of the in_time and out_Time fields

Please help me on this
Re: rows between 2 times [message #432990 is a reply to message #432989] Sat, 28 November 2009 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Please help how to extract the time field of the in_time and out_Time fields

Is this not what has been posted all over this topic?

Here's another way:
SQL> with hour as (select sysdate dt, to_number(to_char(sysdate,'HH24')) hour from dual)
  2  select dt,
  3         case 
  4           when hour between 7 and 12 then 2
  5           when hour between 13 and 22 then 3
  6           else 1
  7         end shift
  8  from hour;
DT                       SHIFT
------------------- ----------
28/11/2009 10:43:13          2

1 row selected.

Regards
Michel
Re: rows between 2 times [message #432991 is a reply to message #432990] Sat, 28 November 2009 04:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Besides, you do not need to extract the hour as I showed you over and over.
Re: rows between 2 times [message #432999 is a reply to message #432991] Sat, 28 November 2009 10:28 Go to previous messageGo to next message
venkatesh.M.P
Messages: 37
Registered: February 2009
Location: india
Member
above reply worked or not correctly reply pls
Re: rows between 2 times [message #433003 is a reply to message #432999] Sat, 28 November 2009 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
venkatesh.M.P wrote on Sat, 28 November 2009 17:28
above reply worked or not correctly reply pls

What does this mean?
Could you post ALL words and letters?

Regards
Michel

Re: rows between 2 times [message #433016 is a reply to message #433003] Sat, 28 November 2009 18:31 Go to previous message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

Laughing
Previous Topic: Oracle:Performance problem in interminent execution
Next Topic: Inner query return different results when i wrap it up
Goto Forum:
  


Current Time: Fri Sep 30 22:45:52 CDT 2016

Total time taken to generate the page: 0.12996 seconds