SQL> DROP TABLE T;
 
Table dropped

SQL> CREATE TABLE T(TIME_COL TIMESTAMP(6));
 
Table created
SQL> INSERT INTO T VALUES(TO_TIMESTAMP('12:12:12','HH:MI:SS'));
 
1 row inserted
SQL> COMMIT;
 
Commit complete
SQL> SELECT * FROM T;
 
TIME_COL
-------------------------------------------------
01-SEP-13 12.12.12.000000 PM

orclz> create table t1(c1 interval day to second,c2 varchar2(20));

Table created.

orclz> insert into t1 values(interval '90' minute,'01:30');

1 row created.

orclz> select * from t1;

C1                                                                          C2
--------------------------------------------------------------------------- -----------
+00 01:30:00.000000                                                         01:30

orclz>

WITH TEMP AS
 (SELECT TO_DATE('21-09-2013 10:23:11', 'DD-MM-YYYY HH24:MI:SS') entry_time,
         TO_DATE('21-09-2013 18:59:51', 'DD-MM-YYYY HH24:MI:SS') exit_time
    FROM DUAL
  UNION ALL
  SELECT TO_DATE('21-09-2013 13:24:31', 'DD-MM-YYYY HH24:MI:SS') entry_time,
         TO_DATE('21-09-2013 19:33:10', 'DD-MM-YYYY HH24:MI:SS') exit_time
    FROM DUAL
  UNION ALL
  SELECT TO_DATE('21-09-2013 10:00:51', 'DD-MM-YYYY HH24:MI:SS') ENTRY_TIME,
         TO_DATE('21-09-2013 19:00:43', 'DD-MM-YYYY HH24:MI:SS') EXIT_TIME
    FROM DUAL)
SELECT *
  FROM TEMP
 WHERE to_char(entry_time, 'hh24miss') >= 100000
   and to_char(exit_time, 'hh24miss') <= 190000;

create table dept_time(start_time number,end_time number,dept varchar2(50));

insert into DEPT_TIME values (100000,190000,'SALESS');
insert into DEPT_TIME values(90000,180000,'FINANCE');
insert into dept_time VALUES(113000,203000,'IT');
insert into dept_time VALUES(143000,233000,'SUPPORT');

create or replace procedure validate_entry_time(p_entry_time date default sysdate ,p_dept in varchar2,p_status out varchar2) is
l_entry_time number;
l_start_time number;
l_end_time number;
l_dept varchar2(50);
begin
l_entry_time:=to_number(to_char(sysdate,'hh24miss'));
select start_time,end_time,dept into l_start_time,l_end_time,l_dept from dept_time where dept=p_dept;
if l_entry_time not between l_start_time and l_end_time then 
p_status:='Invalid';
ELSE
p_status:='Valid';
end if;
end;

declare
p_status varchar2(100);
begin
validate_entry_time(null,'IT',p_status);
dbms_output.put_line('status ' ||p_status);
end;

create table dept_time(start_time number,end_time number,dept varchar2(50));

insert into DEPT_TIME values (100000,190000,'SALESS');
insert into DEPT_TIME values(90000,180000,'FINANCE');
insert into dept_time VALUES(113000,203000,'IT');
insert into dept_time VALUES(143000,233000,'SUPPORT');


 CREATE OR replace PROCEDURE Validate_entry_time(
p_entry_time DATE DEFAULT SYSDATE,
p_dept       IN VARCHAR2,
p_status     OUT VARCHAR2)
IS
  l_entry_time NUMBER;
  l_start_time NUMBER;
  l_end_time   NUMBER;
  l_dept       VARCHAR2(50);
BEGIN
    l_entry_time := To_number(To_char(SYSDATE, 'hh24miss'));

    SELECT start_time,
           end_time,
           dept
    INTO   l_start_time, l_end_time, l_dept
    FROM   dept_time
    WHERE  dept = p_dept;

    IF l_entry_time NOT BETWEEN l_start_time AND l_end_time THEN
      p_status := 'Invalid';
    ELSE
      p_status := 'Valid';
    END IF;
END;  


declare
p_status varchar2(100);
begin
validate_entry_time(null,'IT',p_status);
dbms_output.put_line('status ' ||p_status);
end;

l_entry_time := To_number(To_char(SYSDATE, 'hh24miss'));
replace with
l_entry_time := To_number(To_char(p_entry_time, 'hh24miss'));

 orclz>
orclz> select sysdate from dual;

SYSDATE
-------------------
2013-09-23 08:47:08

orclz> select (To_char(SYSDATE, 'hh24miss')) from dual;

(TO_CH
------
084718

orclz> select To_number(To_char(SYSDATE, 'hh24miss')) from dual;

TO_NUMBER(TO_CHAR(SYSDATE,'HH24MISS'))
--------------------------------------
                                 84721

orclz>

SELECT *
FROM dept_time
WHERE TO_NUMBER(TO_CHAR(SYSDATE,'HH24MISS')) BETWEEN start_time AND end_time
AND dept='SUPPORT'; --invalid one

SELECT *
FROM dept_time
WHERE TO_NUMBER(TO_CHAR(SYSDATE,'HH24MISS')) BETWEEN start_time AND end_time
AND dept='IT'; --valid one

DECLARE
    p_status VARCHAR2(100);
    p_emp_id VARCHAR2(30);
BEGIN
    INSERT INTO employee_time(entry_time,exit_time, status,emp_id)
    VALUES     (SYSDATE, NULL, NULL,p_emp_id);--first time emtry

    Validate_entry_time(NULL, 'IT', p_status);

      UPDATE employee_time SET status = p_status,exit_time = SYSDATE
      WHERE  employee_id = p_emp_id; --status updating for entyr or exit
END;  

SQL> CREATE TABLE dept_time
  2    (
  3       start_time TIMESTAMP,
  4       end_time   TIMESTAMP,
  5       dept       VARCHAR2(50)
  6    );

Table created.

SQL>
SQL> INSERT INTO dept_time
  2  VALUES      (To_timestamp(Trunc(SYSDATE)
  3                            ||' '
  4                            ||'10:00:00 AM'),
  5               To_timestamp(Trunc(SYSDATE)
  6                            ||' '
  7                            ||'7:00:00 PM'),
  8               'SALES');

1 row created.

SQL>
SQL> INSERT INTO dept_time
  2  VALUES      (To_timestamp(Trunc(SYSDATE)
  3                            ||' '
  4                            ||'7:00:00 AM'),
  5               To_timestamp(Trunc(SYSDATE)
  6                            ||' '
  7                            ||'4:00:00 PM'),
  8               'FINANCE');

1 row created.

SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT dept,
  2         To_char(SYSDATE, 'hh12:mi:ss AM') curr_time,
  3         CASE
  4           WHEN systimestamp BETWEEN To_timestamp(Trunc(SYSDATE)
  5                                                  ||' '
  6                                                  ||To_char(start_time,
  7                                                    'hh12:mi:ss AM'))
  8                                     AND
  9                                            To_timestamp(
 10                                            Trunc(SYSDATE)
 11                                            ||' '
 12                                            ||
 13                                            To_char(end_time, 'hh12:mi:ss AM'
))
 14         THEN
 15           'valid'
 16           ELSE 'invalid'
 17         END                               status
 18  FROM   dept_time;

DEPT                                               CURR_TIME   STATUS
-------------------------------------------------- ----------- -------
SALES                                              05:55:42 PM valid
FINANCE                                            05:55:42 PM invalid