year month days

select join_dt, sysdate         ,trunc(months_between(sysdate, join_dt) / 12) as yrs
         ,trunc(mod(months_between(sysdate, join_dt), 12)) as mnths
        ,trunc(sysdate - add_months(join_dt, trunc(months_between(sysdate, join_dt)))) as dys from emp_mast where emp_id='010001'

 JOIN_DT   SYSDATE          YRS      MNTHS        DYS
--------- --------- ---------- ---------- ----------
01-NOV-10 24-AUG-17          6          9         23

How do I write a SQL to display the number of months between two dates

Answer:  Here is a simple way to display all months between a range of dates, without referencing any table:
SELECT
   MOD( TRUNC( MONTHS_BETWEEN( '2015-07-29', '2015-03-28' ) ), 12 ) as MONTHS
FROM DUAL;
Here is a way to name all of the months between two dates:
select to_char(which_month, 'Mon-yyyy') month
from
(
select
add_months(to_date('01-2016','mm-yyyy'), rownum-1) which_month
from
dba_objects
where
rownum <= months_between(to_date('12-2016','mm-yyyy'), add_months(to_date('01-2016','mm-yyyy'), -1))
order by
which_month
);

MONTH
--------
Jan-2016
Feb-2016
Mar-2016
Apr-2016
May-2016
Jun-2016
Jul-2016
Aug-2016
Sep-2016
Oct-2016
Nov-2016

MONTH
--------
Dec-2016

12 rows selected.

 
Here is a script that uses the last_day function to show the number of months between two dates: 

select
   distinct(last_day(to_date(td.end_date + 1 - rownum)))
from
   all_objects,
   (-- this is just to easily substitute dates for the example...
      select to_date('30-JAN-2010') start_date
            ,to_date('15-MAR-2011') end_date
      FROM   DUAL  ) td
where
   trunc(td.end_date + 1 - rownum,'MM') >= trunc(td.start_date,'MM')
order by 1


(LAST_DAY
---------
31-JAN-10
28-FEB-10
31-MAR-10
30-APR-10
31-MAY-10
30-JUN-10
31-JUL-10

How to find out two date month number in oracle database

function AVG_SFTFormula return Number is
    V     NUMBER;
begin
    SELECT TRUNC(MONTHS_BETWEEN( :P_ENDDT, :P_stdt))
    INTO V
    FROM DUAL;
  
   IF V <1 br="" then="">       RETURN(0);
   ELSE
        RETURN(NVL(:TOT_SFT,0)/NVL(V,0));
   END IF;
  
  
end;


---------------
function AVG_SFTFormula return Number is
    V     NUMBER;
begin
    select 12+(to_char(MAX(TRUNC(to_date(MDATE),'RR')),'MM')-to_char(min(to_date(MDATE)),'MM'))
    INTO V
    from sales_master where slcode=:SLCODE AND MDATE BETWEEN :P_stdt AND :P_ENDDT;
  
   IF V <1 br="" then="">       RETURN(0);
   ELSE
        RETURN(NVL(:TOT_SFT,0)/NVL(V,0));
   END IF;
  
  
end;

How to set company wise month

SQL> select MAX(ADD_MONTHS(trunc(at_dt,'MON')+25,-1)) into :dt1 from emp_attn_data
  2    where at_dt <> (select max(add_months(trunc(at_dt,'mon')+25,0)) from emp_attn_data);

MAX(ADD_M
---------
26-JUL-17

SQL> select MAX(ADD_MONTHS(trunc(at_dt,'MON')+25,-1)) into :adt from emp_attn_data;

MAX(ADD_M
---------
26-JUL-17

SQL> select MAX(trunc(at_dt,'MON')+24) into :adt from emp_attn_data;

MAX(TRUNC
---------
25-AUG-17

How to center the oracle forms 10g (windows)

when-new-forms-instance----------------------------------

Declare
       
  v_window      varchar2(100):='WINDOW1';
    v_width                number(4);
    v_height            number(4);
    vw                        number(4);
    vh                        number(4);
   
BEGIN
    SET_WINDOW_PROPERTY(FORMS_MDI_WINDOW,WINDOW_STATE,MAXIMIZE);
  SET_WINDOW_PROPERTY('WINDOW1',WINDOW_STATE,MAXIMIZE);
    set_window_property(forms_mdi_window,title,'STAR CERAMICS LIMITED                                    HUMAN RESOURCE MANAGEMENT SYSTEM (HRMS)');
    vw:=gET_application_PROPERTY(DISPLAY_WIDTH)-50;
    vh :=gET_application_PROPERTY(DISPLAY_HEIGHT)-180;

    v_width := Get_Window_Property(v_WINDOW, WIDTH);
    v_height:= Get_Window_Property(v_WINDOW, HEIGHT);

  Set_Window_Property(v_WINDOW, POSITION, (vw-v_width)/2, (vh-v_height)/2) ;
 
      replace_menu(:global.path||'HRM10g\Attendance\Forms\atten_menu.mmx');
END;



exit btn
------------
replace_menu();
exit_form(no_validate);

Programming Check

1---formula column  report-------------------------
function CF_mastercodeFormula return Char is
v varchar2(150);
begin
    select  :itemname||'('|| BR_ITEM_MAPPING.MASTERCODE ||')' into v 
    from br_item_mapping
  where itemcode=:itemcode;
  return(v);
exception
    when others then
    return (:itemname);
end;

--------------
2----button level change
-------------------------------
--must be control block no
--item key board/item navigator no


if :SYSTEM.MODE='NORMAL' then
    CLEAR_FORM(NO_VALIDATE);
     set_item_property('CONTROL.QRY_BTN',label,'Query');
     --do_key('enter_query');
     enter_query;
else
    go_block('BR_CUSTOMER');
        --do_key('execute_query');
    execute_query;
    set_item_property('CONTROL.QRY_BTN',label,'Search');
end if;


-------------------

3----item level when-mouse-double-click

declare
     ed_id editor;
     ed_name varchar2(40);
     val varchar2(32000);
     ed_ok boolean:=true;
begin
     ed_name:='ED_ADDR';
     ed_id:=find_editor(ed_name);
     val:=:BR_CUSTOMER.ADDRESS;
     show_editor(ed_id,val,10,14,val,ed_ok);
     :BR_CUSTOMER.ADDRESS:=val;
end;

------------------------------------

4---enable/disable
---------------------

set_item_property ('pre_btn', visible, property_true);
set_item_property ('nxt_btn', visible, property_true);
set_item_property('pre_btn',enabled,property_true);
set_item_property('nxt_btn',enabled,property_true);

5----------------------
---post-change tirgger

if :lv_cd is not null then
    select lv_nm into :lv_nm from level_info where lv_cd=:lv_cd;
    select LPAD(NVL(max(exam_cd),0)+1,3,0) into :grd_cd from exam_info;
else
    message('Select exam level ');
    raise form_trigger_failure;
end if;


6-----------------------
------program unit or key next item trigger

------DATA ALREADY INSERTED

PROCEDURE DIVS IS
dnm char(40);
BEGIN
begin
  select cost_desc into dnm
  from
  cost
  where cost_sl=:emp_div.cost_cd;
exception
    when no_data_found
    then
    dnm :=null;
END;
if dnm is not null
    then :emp_div.cost_nm :=dnm;
      message('DATA ALREADY INSERTED');
        message('DATA ALREADY INSERTED');
    raise form_trigger_failure;
else
    :emp_div.cost_nm :=dnm;
    next_field;
end if;
end;



7---------
-------WHEN-NEW-BLOCK-INSTANCE  THANA HRM

execute_query;
last_record;
next_record;


8--------
---------Invalid Examination Code


declare
    bb char(40);
begin
    select exam_name into bb
    from exam_info
    where exam_cd=:grd_cd and lv_cd = :lv_cd;
if bb is not null
    then :grd:=bb;
    next_field;
else
        raise form_trigger_failure;
    :grd_cd :=null; 
    :grd :=null;
    message('Invalid Examination Code');
    end if;
end;



9--------
-------form_success

PROCEDURE grd_code IS
BEGIN
 if form_success then
         copy('5','system.message_level');
         COMMIT_FORM; next_record;
         message('Data commited !');
         message('Data commited !');
 else
     message('Problem in Data ! Check your Data !');
     message('Problem in Data ! Check your Data !');
end if;
END;

10------------

----------INVALID COST CENTRE CODE

PROCEDURE cost_code IS
    bb char(45);
 begin
null;
    select cost_desc into bb
    from cost
    where cost_sl=:emp_desg.cost_cd;
if bb is not null
    then :emp_desg.COST_NM :=bb;
    next_field;
else
        raise form_trigger_failure;
    :emp_desg.cost_cd :=null; 
:emp_desg.cost_nm :=null;
    message('INVALID COST CENTRE CODE');
    end if;
end;


11-------

when-validate-item
-------------------

begin
if :emp_mast.div_cd is not null then
        message('Are you sure Employee Division is Correct...??');
        message('Are you sure Employee Division is Correct...??');
    else
    null;
    raise form_trigger_failure;
end if;
end;

--message


12---------------
---INVALID EMPLOYEE ID

PROCEDURE EMP_CODE2 IS
BEGIN
begin
select a.emp_nm,a.desg_cd, c.desg_nm,a.dept_cd,f.dept_nm,a.sec_cd,g.sec_nm,a.cost_sl,h.cost_desc
into
:emp_desg.NM,:emp_desg.deg,:emp_desg.deptnm1,:emp_desg.dept_cd,:emp_desg.dept,
:emp_desg.sec_cd,:emp_desg.sec,:emp_desg.cost_cd,:emp_desg.cost_nm
from emp_mast a,emp_desg c,emp_dept f,emp_sec g,cost h
where a.emp_id=:emp_id and
        a.desg_cd=c.desg_cd and
      a.dept_cd=f.dept_cd(+)  and
      a.dept_cd=g.dept_cd(+) and
      a.sec_cd=g.sec_cd(+)  and
      a.cost_sl=h.cost_sl;
      --pbas;
next_field;
EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
        message('INVALID EMPLOYEE ID !  ');
raise form_trigger_failure;
END;
 end;   


----13 DATA ALRADY POSTED TRY WITH NEW ONE
---------------------------------------------

PROCEDURE ptp_code IS
BEGIN
  select a.dept_cd,c.dept_nm,a.sec_cd,d.sec_nm,
         a.cost_sl,e.cost_desc,a.pt_dt,a.flg_tp
into :emp_desg.dept_cd,:emp_desg.dept,
:emp_desg.sec_cd,:emp_desg.sec,:emp_desg.cost_cd,:emp_desg.cost_nm,
:emp_desg.birth_dt,:emp_desg.tp
from emp_posting a,emp_dept c,emp_sec d,cost e
where a.com_cd = :com_cd and a.emp_id=:emp_desg.emp_id and
      a.cir_no=:emp_desg.circ_no and
      a.cir_dt=:emp_desg.doj and
      a.dept_cd=c.dept_cd and
      a.dept_cd=d.dept_cd and
      a.sec_cd=d.sec_cd and
      a.cost_sl=e.cost_sl;
 message('DATA ALRADY POSTED TRY WITH NEW ONE');
 raise form_trigger_failure;
exception
    when no_data_found
    then
    next_field;
END;



----------------
error set in oracle forms 10g
------------------------------
---ON-ERROR trigger form level


DECLARE
   lv_errcod  NUMBER       := ERROR_CODE;
   lv_errtyp  VARCHAR2(3)  := ERROR_TYPE;
   lv_errtxt  VARCHAR2(80) := ERROR_TEXT;
BEGIN
   IF (lv_errcod = 40508) THEN
       message('UNABLE TO INSERT RECORD: DUPLICATE CODE ENTRY');
       message('UNABLE TO INSERT RECORD: DUPLICATE CODE ENTRY');
       RAISE Form_Trigger_Failure;
    ELSIF (lv_errcod = 40510) THEN
        message('UNABLE TO DELETE RECORD: DATA EXISTS IN SUB GROUP');
       message('UNABLE TO DELETE RECORD: DATA EXISTS IN SUB GROUP');
       RAISE Form_Trigger_Failure;
    ELSIF (lv_errcod = 40509) THEN
       message('UNABLE TO UPDATE RECORD: DATA EXISTS IN SUB GROUP / DUPLICATE RECORD');
       message('UNABLE TO UPDATE RECORD: DATA EXISTS IN SUB GROUP / DUPLICATE RECORD');
       RAISE Form_Trigger_Failure;
    ELSE
   Message(lv_errtyp||'-'||to_char(lv_errcod)||': '||lv_errtxt);
   Message(lv_errtyp||'-'||to_char(lv_errcod)||': '||lv_errtxt);
   RAISE Form_Trigger_Failure;
    END IF;
END;



/

---------------------------------------
values set

---WHEN-VALIDATE-ITEM

if :slcode is not null then
    select nvl(max(mlink),0)+1 into :mlink from sl_code;        -----mlink number
else
    null;
end if;


--------
HRM(star)
----
when-validate_item

declare
    v number;
begin
    select count(distinct at_dt) into v from at_proc_mast where at_Dt =:dtd;
   
    if v>0 then
        message('Data Already Process...');
        RAISE FORM_TRIGGER_FAILURE;
    else
        null;
    end if;
   
end;



----set pre insert/ auto numner lpad 0001
--------------------------------------------

begin
    select lpad(nvl(to_number(max(mv_no)+1),1),4,('0'))
into :mov_info.mv_no
    from tour_info
    where at_dt=:mov_info.at_dt;
exception
    when no_data_found then
    null;
end;




end dt is grater then st_dt/at_dt then null
----------------------------------------------


begin
if :mov_info.edt<:mov_info .at_dt="" br="" then="">    raise form_trigger_failure;
else
    null;
    end if;
end;



----------------------------------------------
----validate date

declare
    v number;
begin
    select count(distinct at_dt) into v from at_proc_mast where at_Dt =:dtd;
   
    if v>0 then
        message('Data Already Process...');
        raise form_trigger_failure;
    else
        null;
    end if;
end;


--------


declare
    v number;
begin

    SELECT count(cust_id) INTO V FROM br_customer WHERE cust_id=:BR_CUSTOMER.cust_id;
    if v>0 then
            MESSAGE('Cusomer Id Already Exist !!');
          MESSAGE('Cusomer Id Already Exist !!');   
        raise form_trigger_failure;
    else
    next_field;
    end if;
end;

-------------------------------------------------


pre_insert
-------------

field varchar2
SELECT NVL(MAX(TO_NUMBER(recpt_no)),0)+1 into :br_receipt_master.recpt_no from br_receipt_master;


id restriction
---------------
declare
    v varchar2(6);
begin
    select emp_id into v from hrm.emp_mast where emp_id='001032';
    if :auth_by=v then
select emp_nm into :auth_nm from hrm.emp_mast where emp_id = :auth_by;
    else
        :auth_nm:='';
        :auth_by:='';
    end if;
    end;
next_field;


------------

set field data form an others field
----------------------------------

go_block('emp_attn_data');
FIRST_RECORD;
-- WHILE TRUE LOOP
WHILE :System.Last_Record <> 'TRUE' LOOP
    if :emp_attn_data.flag is not null then
        null;
    elsif  :emp_attn_data.fst_in<>'000000' then
        null;
    else
        :emp_attn_data.fst_in:=:con.in_time;
        :emp_attn_data.fst_out:=:con.out_time;
    end if;
NEXT_RECORD;
IF :System.Last_Record = 'TRUE' THEN
    if :emp_attn_data.flag is not null then
        null;
    elsif  :emp_attn_data.fst_in<>'000000' then
        null;
    else
    :emp_attn_data.fst_in:=:con.in_time;
    :emp_attn_data.fst_out:=:con.out_time;
    end if;
EXIT;
END IF;
END LOOP;
first_record;



/

when-new-form_instarnce
-----------------------
:clock:=to_char(sysdate,'HH12:MI:SS');


-----------valid & invalid -----------

declare
    v number;
begin
    select dept_cd into v  from emp_dept where dept_cd=:dept_cd;
   
if v>0 then
  select dept_nm into :dept_nm from emp_dept where dept_cd=:dept_cd;

else
    message('Invalid Department id !!');
end if;
exception
    when others then
    raise form_trigger_failure;
end;


/

between date check
-----------------------
table data
-----------
26-JUL-17    25-AUG-17
26-AUG-17    25-SEP-17
26-SEP-17    25-OCT-17



declare
v number;

begin
    select count(*)  into v from month where end_dt>=:emp_desg.st_dt and end_dt <:emp_desg .end_dt="" br="">
if v>0  then
    message('End Date must be less then or equal End Month ');
    message('End Date must be less then or equal End Month ');
    clear_form;
else   
:emp_desg.ap_day:=(:emp_desg.end_dt-:emp_desg.st_dt)+1;
next_item;
end if;
end;

 

Oracle 12c Architecture

http://oracle-help.com/oracle-12c/oracle-12c-architecture/