Date related functions

1. report rdf related:
field/parameter type: date
Input mask: yyyy/mm/dd hh24:mi:ss

2. apps parameter value set:
fnd_standard_date

3. date related in sql:
month first date trunc(sysdate,'mm')
month last date last_day(sysdate)
year first date   trunc(TO_DATE(:P_YEAR,'YYYY'),'YEAR') and ADD_MONTHS(trunc(TO_DATE(:P_YEAR,'YYYY'),'YEAR'),12)-1
year last date ADD_MONTHS(trunc(TO_DATE(2013,'YYYY'),'YEAR'),12)-1


4. fffunc.days_between

5.
Arabic:
Initcap(TO_CHAR(TO_DATE(:P_MM_YYYY,'MM-YYYY'),'MONTH-YYYY','nls_date_language=Egyptian')) Month_Arabic
English:
Arabic:
Initcap(TO_CHAR(TO_DATE(:P_MM_YYYY,'MM-YYYY'),'MONTH-YYYY'))

6.
use of date in the anonymous block without format :

declare
LEAVE_TYPE_OLD varchar2(200) := 'إجازة سنوية';
ASSIGNMENT_ID NUMBER := 13117;
LEAVE_START DATE := TO_DATE('01-DEC-2014');
LEAVE_END DATE := TO_DATE('20-DEC-2014');
JOIN_DATE DATE := TO_DATE('15-DEC-2014');
begin
if LEAVE_TYPE_OLD = 'إجازة سنوية' and SIGN (JOIN_DATE - LEAVE_END) IN (0,-1) THEN
              DBMS_OUTPUT.PUT_LINE(SIGN (JOIN_DATE - LEAVE_END));
               BEGIN
                    XXPY_UPDATE_EMP_ASSIGNMENT.CRT_ELE_ENTRY_ANNL_LEV_BAL
                    (P_ASSIGNMENT_ID => ASSIGNMENT_ID
                     ,P_LEAVE_START  => LEAVE_START
                     ,P_LEAVE_END  => LEAVE_END
                     ,P_JOIN_DATE  => JOIN_DATE
                     ,P_LEAVE_TYPE_OLD => LEAVE_TYPE_OLD
--                     ,P_EXTENDED_DATE DATE DEFAULT NULL,
--                     ,P_LEAVE_TYPE VARCHAR2 DEFAULT NULL, --EXTENDED_LEAVE_TYPE
--                     ,P_HOLD_SALARY_ENTRY_ID VARCHAR2 DEFAULT NULL,
--                     ,P_REPAY_HOLD VARCHAR2 DEFAULT NULL
                     );
                    
                     DBMS_OUTPUT.PUT_LINE(ASSIGNMENT_ID);
EXCEPTION WHEN OTHERS THEN NULL;                    
                     DBMS_OUTPUT.PUT_LINE(SQLERRM);

end;                    
END IF;
END;


7. to get year wise data.
SELECT DISTINCT TO_CHAR (TO_DATE (end_date, 'DD-MON-RRRR'), 'MON-RRRR' ) pab_end_date
              FROM v_leave_details         WHERE end_date IS NOT NULL
          ORDER BY CASE
                      WHEN TO_CHAR (TO_DATE (end_date, 'DD-MON-RRRR'), 'MON-RRRR'
                                   ) LIKE '%2014'
                         THEN 1
                      WHEN TO_CHAR (TO_DATE (end_date, 'DD-MON-RRRR'),  'MON-RRRR'
                                   ) LIKE '%2013'
                         THEN 2
                      WHEN TO_CHAR (TO_DATE (end_date, 'DD-MON-RRRR'),  'MON-RRRR'
                                   ) LIKE '%2012'
                         THEN 3
                      WHEN TO_CHAR (TO_DATE (end_date, 'DD-MON-RRRR'),  'MON-RRRR'
                                   ) LIKE '%2011'
                         THEN 4
                      WHEN TO_CHAR (TO_DATE (end_date, 'DD-MON-RRRR'),  'MON-RRRR'
                                   ) LIKE '%2010'
                         THEN 5
                      ELSE 6
                   END;

8. Useful queries.
                 NVL(SUBSTR(haou.NAME,1,INSTR(haou.NAME,'/')-1),haou.NAME) FIRST_PART,
                 NVL(SUBSTR(haou.NAME,INSTR(haou.NAME,'/')+1,100),haou.NAME) SECOND_PART,               

9. select fnd_conc_date.string_to_date( '21-Dec-2009') as date_value from dual ;
http://apps2fusion.com/apps/apps/430-string-to-date

10. FND_DATE.canonical_to_date

11.fnd_conc_date.string_to_date('31-05-2014')

12. Usage of FND_STANDARD_DATE Value set for the PL/SQL package/procedure passing Date as parameter
In Procedure chagne IN parameters from Date to Varchar2
example: P_DATE_FROM Varchar2,

Then use fnd_conc_date.string_to_date(P_DATE_FROM) to change it to date.