Convert multiple date varchar2 to date format in Oracle 11g

Question

I am having an issue trying to convert multiple dates to one defined format. We are receiving the multiple dates from another DB source so I do not have control of the formatting until it reaches ours.

Here are all the formats:

YYYYMMDD

YYYY-MM-DD HH:MM:SS

MM/DD/YYYY

MM-DD-YYYY

Abrieviated Day Month DD HH:MM:SS TimeZone YYYY ('Thu Feb 02 20:49:59 MSK 2012')

Fully written Day, Month DD, YYYY HH:MM:SS AM/PM

My requirement is to set them all to the standard MM/DD/YYYY format or null. Any ideas?

Thank you.

Answer 1

You may define a transformation function, basically processing sequentially each format:

create or replace function translate_date(i_date_string VARCHAR2) return date as
begin
-- you may optimize to not to go in all blocks based on the string format
-- order the blocks on the expected frequency
 begin
   return to_date(i_date_string,'yyyymmdd');
 EXCEPTION
   WHEN OTHERS  THEN NULL;
 end;  
 begin
   return to_date(i_date_string,'yyyy/mm/dd');
 EXCEPTION
   WHEN OTHERS  THEN NULL;
 end; 
 begin
   return to_date(i_date_string,'yyyy-mm-dd');
 EXCEPTION
   WHEN OTHERS  THEN NULL;
 end; 
 begin
   return to_date(i_date_string,'yyyy-mm-dd hh24:mi:ss');
 EXCEPTION
   WHEN OTHERS  THEN NULL;
 end; 
 begin
 -- transform to local timestamp and than to date
   return cast(cast(to_timestamp_tz(i_date_string,'dy month dd hh24:mi:ss tzr yyyy') as TIMESTAMP WITH LOCAL TIME ZONE) as date);
 EXCEPTION
   WHEN OTHERS  THEN NULL;
 end; 
 begin
   return to_date(i_date_string,'dy, month dd, yyyy hh:mi:ss am');
 EXCEPTION
   WHEN OTHERS  THEN NULL;
 end;  
 return NULL;
end;
/

for example for sample data

 TSTMP                                                                                              
 ------------------------
 20150101                                                                                             
 2015-01-01 23:59:59                                                                                  
 2015/01/01                                                                                           
 2015-01-01                                                                                           
 Thu Feb 02 20:49:59 Europe/Moscow 2012                                                               
 Thu, Feb 02, 2012 10:49:59 AM                                                                        
 Thu, Feb 02, 2012 10:49:59 PM  

you get

TSTMP                                       RESULT_DATE       
------------------------------------------  -------------------
20150101                                    01.01.2015 00:00:00 
2015-01-01 23:59:59                         01.01.2015 23:59:59 
2015/01/01                                  01.01.2015 00:00:00 
2015-01-01                                  01.01.2015 00:00:00 
Thu Feb 02 20:49:59 Europe/Moscow 2012      02.02.2012 17:49:59 
Thu, Feb 02, 2012 10:49:59 AM               02.02.2012 10:49:59 
Thu, Feb 02, 2012 10:49:59 PM               02.02.2012 22:49:59 

Note that I skipped the case with time zone abbraviation (MSK), see possible solution in the answer from @Sentinel, but check Conversion of String with Abbreviated Timezone to Timestamp that this may be ambiguous.

Answer 2

I'd suggest using a case statement with regexp_like conditions to detect likely formats and return dates using the appropriate date mask in the then clauses e.g.:

with tz as (
SELECT distinct tzabbrev
     , first_value(min(tzname)) over (partition by tzabbrev order by count(*) desc) tzname
  FROM v$timezone_names 
 group by tzabbrev
     , TZ_OFFSET(tzname)
), dta as (
select yt.install_date
     , regexp_replace(yt.install_date,tzabbrev,tzname,1,1,'i') install_date2
  from your_table yt
  left join tz
    on regexp_like(install_date, tz.TZABBREV,'i')
)
select install_date, install_date2
     , to_timestamp_tz( install_date2
              , case 
                  when regexp_like(install_date2,'^[A-Z]{3,} [A-Z]{3,} [0-9]{1,2} [0-9]{1,2}(:[0-9]{2}){1,2} [[:print:]]{5,} [0-9]{2,4}','i') then 'DY MON DD HH24:MI:SS TZR YYYY'
                  when regexp_like(install_date2,'^[A-Z]{4,},? [A-Z]{3,},? [0-9]{1,2},? [0-9]{2,4}','i') then 'DAY MONTH DD YYYY'
                  when regexp_like(install_date2,'^[A-Z]{3},? [A-Z]{3,},? [0-9]{1,2},? [0-9]{2,4}','i') then 'DY MONTH DD YYYY'
                  when regexp_like(install_date2,'^[0-9]{1,2}[-/][0-9]{1,2}[-/]([0-9]{2}){1,2}') then 'MM-DD-RRRR'
                  when regexp_like(install_date2,'^[0-9]{1,2}[-/ ][A-Z]{3,}[-/ ]([0-9]{2}){1,2}','i') then 'DD-MON-RRRR'
                  when regexp_like(install_date2,'^[A-Z]{3,}[-/ ][0-9]{1,2},?[-/ ]([0-9]{2}){1,2}','i') then 'MON-DD-RRRR'
                  when regexp_like(install_date2,'^(19|20)[0-9]{6}') then 'RRRRMMDD'
                  when regexp_like(install_date2,'^[23][0-9]{5}') then 'DDMMRR'
                  when regexp_like(install_date2,'^[0-9]{6}') then 'MMDDRR'
                  when regexp_like(install_date2,'^[01][0-9]{7}') then 'MMDDRRRR'
                  when regexp_like(install_date2,'^[23][0-9]{7}') then 'DDMMRRRR'
                  ELSE NULL
                end
              ||case
                  when regexp_like(install_date2, '[0-9]{1,2}(:[0-9]{2}){1,2}$') then ' HH24:MI:SS'
                  when regexp_like(install_date2, '[0-9]{1,2}(:[0-9]{2}){1,2} ?(am|pm)$','i') then ' HH:MI:SS AM'
                  else null
                end
              )
              Install_Time_Stamp
  from dta;

I had issues with the time zone abbreviations so I added a step to replace them with time zone regions first.

Answer 3

The function can be simplified a little bit as to_date tolerates minor deviations (different separators, missing separators, missing time components, 2/4-digit year). For instance to_date(:str,'rrrr-mm-dd hh24:mi:ss') will cover

2020/09/18 01.02
2020.09.18 01
20200918010203
2020-0901
202009-01
20/09/18

To_timestamp_tz will also tolerate missing milliseconds and time zone (including missing TZ elements TZM and TZD). So we basically need to take care of major variations only like hh24/hh, mm/mon, elements order, "T" separator in ISO 8601 (2020-01-01T01:02:03), TZ designators (UTC offset TZH:TZM / region name TZR), and day of week (DY).