Oracle date function(Attendance related).

Note : Your database table name  hr_biometricattendancecnt it has one field login_time
this query give you first login time and last login time.


select to_char(first_login,'HH24:Mi:SS AM')First,to_char(last_login,'HH24:Mi:SS AM') Last from
(select min(LOGIN_TIME) first_login,max(LOGIN_TIME) last_login from hr_biometricattendancecnt
where trunc(log_date)=trunc(:d)
group by trunc(log_date))


Purpose : If you want to get sysdate as your day format like this code will be give you desire.


select decode(trim(to_char(to_date(sysdate),'DAY')),'SATURDAY','SAT_DAY','SUNDAY','SUN_DAY','MONDAY','MON_DAY','TUESDAY','TUE_DAY', 'WEDNESDAY','WED_DAY','THURSDAY','TUS_DAY','FRIDAY','FRI_DAY')  from dual;

Output :  TUE_DAY.
select trim(to_char(to_date(sysdate),'DAY')) from dual;   OUTPUT: TUESDAY
select to_char(to_date(sysdate),'DAY') from dual;    OUTPUT : TUESDAY

HELPING FUNCTION FOR MORE :

CREATE OR REPLACE function fnc_doc_active(p_date date,p_otno varchar2)
return number is
    var varchar2(100);
    counter number;
    v_sql VARCHAR2(4001);
begin
    
     select decode(trim(to_char(to_date(nvl(p_date,sysdate)),'DAY')),'SATURDAY','SAT_DAY','SUNDAY','SUN_DAY','MONDAY','MON_DAY','TUESDAY','TUE_DAY',
           'WEDNESDAY','WED_DAY','THURSDAY','TUS_DAY','FRIDAY','FRI_DAY')
     into var
     from dual;   

    v_sql := 'select count(1)
              from ot_weekend
              where  '||var||' = ''Y''
              and OT_NO = '||p_otno||' ';
   
    EXECUTE IMMEDIATE v_sql
    INTO counter;
   
    return counter;
exception
    when others then return null;   
end;
/

---Same work as

CREATE OR REPLACE FUNCTION FNC_ROOMWEEKFLAG (p_room_no varchar,p_day date )
return int
as
 v_s char(1);
 v_n char(1);
 v_m char(1);
 v_t char(1);
 v_w char(1);
 v_h char(1);
 v_f char(1);
begin

select SAT_DAY,SUN_DAY,MON_DAY,TUE_DAY,WED_DAY,TUS_DAY,FRI_DAY into v_s,v_n,v_m,v_t,v_w,v_h,v_f
from ot_weekend where  OT_NO=p_room_no;

if to_char(p_day,'DY')    ='SAT' and nvl(v_s,'N')='Y'  then
   
    return 1;
   
elsif to_char(p_day,'DY') ='SUN' and nvl(v_n,'N')='Y'  then

    return 1;
   
elsif to_char(p_day,'DY') ='MON' and nvl(v_m,'N')='Y'  then

    return 1;   
   
elsif to_char(p_day,'DY') ='TUE' and nvl(v_t,'N')='Y'  then

    return 1;
   
elsif to_char(p_day,'DY') ='WED' and nvl(v_w,'N')='Y'  then

    return 1;
   
elsif to_char(p_day,'DY') ='THU' and nvl(v_h,'N')='Y'  then

    return 1;
   
elsif to_char(p_day,'DY') ='FRI' and nvl(v_f,'N')='Y'  then

    return 1;
   
else
    return 0;
end if;

exception when others then
return 0;

end;
/
---22-10-2017

(SELECT to_char(min(to_date(ST_TIME,'HH24:MI')),'HH24:MI') ST_TIME FROM OT_BOOKINGCHDTIME WHERE SLOT_GROUP=A.SLOT_GROUP) START_TIME,
(SELECT to_char(max(to_date(END_TIME,'HH24:MI')),'HH24:MI') END_TIME FROM OT_BOOKINGCHDTIME WHERE SLOT_GROUP=A.SLOT_GROUP) END_TIME

---- to count hour and minutes from a number:

SELECT (TRUNC(((AVG_TIME)/60),0))||':'||MOD((AVG_TIME),60)
---IF YOU WANT TO ADD DATE TIME IN A TABLE FIELD
IN FORMS ITEM PROPERTY WILL BE MUST AS FOLLOWING

FORMAT MASK : DD/MM/YYYY HH24:MI:SS
DATA TYPE :DATETIME