Does anybody know what day this is?

Sometimes dates come in to the database is some unexpected formats.  To help with that I have two function to make a best guess at the format.   The first attempts to determine the format with regular expressions and the second does a to_date using that format and logs any of the date formatting errors.   Additional formats you encounter can be added

create or replace function date_mask( candidate varchar2 ) return varchar2 deterministic as
 vRet varchar2(64) ;
begin
 select value into vRet 
 FROM nls_database_parameters where parameter = 'NLS_DATE_FORMAT' ;
 vRet := case                                           /* Formats are USA centric */ 
 when regexp_like( candidate, '^\d{1,2}/\d{1,2}/\d{2}$') then 'MM/DD/RR'
 when regexp_like( candidate, '^\d{1,2}/\d{1,2}/\d{4}$') then 'MM/DD/YYYY'
 when regexp_like( candidate, '^\d{1,2}-\d{1,2}-\d{2}$') then 'MM-DD-RR'
 when regexp_like( candidate, '^\d{1,2}-\d{1,2}-\d{4}$') then 'MM-DD-YYYY'
 when regexp_like( candidate, '^\d{1,2}-\w{3}-\d{4}$' ) then 'DD-Mon-YYYY'
 when regexp_like( candidate, '^\d{1,2}-\w{3}-\d{2}$' ) then 'DD-Mon-RR'
 when regexp_like( candidate, '^\d{4}-\d{2}-\d{2}$') then 'YYYY-MM-DD'
 when regexp_like( candidate, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$') then 'YYYY-MM-DD HH24:MI:SS'
 when regexp_like( candidate, '^\d{8}$' ) then 'YYYYMMDD'
 else null
 end;
 return vRet;
end;

create or replace FUNCTION safe_to_date( str VARCHAR, pFmt varchar2 default 'DD-Mon-RR' ) return DATE deterministic AS 
 vRet DATE := NULL;
 lFmt VARCHAR2(32); 
 format_date EXCEPTION;
 pragma EXCEPTION_INIT(format_date , -1861);
 date_range EXCEPTION;
 pragma EXCEPTION_INIT(date_range , -1841); 
 non_numeric EXCEPTION;
 pragma EXCEPTION_INIT(non_numeric , -1858); 
 invalid_month EXCEPTION; 
 pragma EXCEPTION_INIT(invalid_month, -1843); 
 invalid_day EXCEPTION;
 pragma EXCEPTION_INIT(invalid_day, -1847);
 invalid_min EXCEPTION; 
 pragma exception_init( invalid_min, -1851 );
 mask_date EXCEPTION; 
 pragma EXCEPTION_INIT(mask_date , -1830);
BEGIN
 lFmt := coalesce( date_mask( str ), pFmt, 'DD-Mon-RR' );
 vRet := to_date( str, lFmt ) ;
 return vRet;
 exception 
 when format_date 
 or date_range 
 or non_numeric 
 or invalid_month 
 or invalid_day
 or mask_date then
-- log takes parameters of ERROR_LEVEL, LOCATION, ERROR_CODE, and MESSAGE  
 --log( 'INFO', $$PLSQL_UNIT, 'ora'||sqlcode, 'ERROR for date:'|| str ||' '||lFmt ||' >'||sqlerrm ) ; 
 RETURN null; 
END;
Bookmark the permalink.

Leave a Reply