Date convert Function

Have created very useful function to convert date format.

We are using it to take the date format from user's preferences and changing it to standard format.




CREATE OR REPLACE FUNCTION APPS.XX_DATE_CONVERT (p_date_format     IN VARCHAR2,
                                               P_PROMISED_DATE      VARCHAR2)
   RETURN VARCHAR2
IS
   p_return_promise_date   VARCHAR2 (100);
BEGIN
   IF p_date_format = 'MM-DD-RRRR'
   THEN
      p_return_promise_date :=
         TO_DATE(TO_CHAR (TO_DATE (P_PROMISED_DATE, 'MM-DD-RRRR HH24:MI:SS'),
                          'DD-MON-RRRR'));
   ELSIF p_date_format = 'DD-MM-RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD-MM-RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'MM/DD/RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'MM/DD/RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'MM.DD.RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'MM.DD.RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'DD-MON-RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD-MON-RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'DD.MM.RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD.MM.RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'DD/MM/RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD/MM/RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'DD/MON/RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD/MON/RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'DD.MON.RRRR'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'DD.MON.RRRR HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR-MM-DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR-MM-DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR-MON-DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR-MON-DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR/MON/DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR/MON/DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR/MM/DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR/MM/DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR.MM.DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR.MM.DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   ELSIF p_date_format = 'RRRR.MON.DD'
   THEN
      p_return_promise_date :=
         TO_CHAR (TO_DATE (P_PROMISED_DATE, 'RRRR.MON.DD HH24:MI:SS'),
                  'DD-MON-RRRR');
   END IF;

   RETURN p_return_promise_date;
EXCEPTION
   WHEN OTHERS
   THEN
      raise_application_error (
         -20001,
         'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM
      );
END;
/

This can be used like :-

XXNHR_DATE_CONVERT (fnd_profile.value('ICX_DATE_FORMAT_MASK'),:PO_SHIPMENTS.PROMISED_DATE)