Tuesday, January 15, 2013

Convert Arabic Hijri date to Gregorian date using nls_calendar!!

--p_hijri_date is varchar2 field
select TO_CHAR(TO_DATE(p_hijri_date,
                          'dd/mm/yyyy',
                          'nls_calendar=''English Hijrah'''),
                  'dd-mm-yyyy',
                  'nls_calendar=''Gregorian''')
from dual;


select TO_CHAR(TO_DATE('06/02/1433',
                          'dd/mm/yyyy',
                          'nls_calendar=''English Hijrah'''),
                  'dd-mm-yyyy',
                  'nls_calendar=''Gregorian''')
from dual;

output- 01-01-2012