在Oracle 11g中将多个日期varchar2转换为日期格式

问题描述

我在尝试将多个日期转换为一种定义的格式时遇到问题.我们正在从另一个数据库来源接收多个日期，因此在格式到达我们之前，我无法对其进行控制.

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.

以下是所有格式:

YYYYMMDD

YYYY-MM-DD HH:MM:SS

YYYY-MM-DD HH:MM:SS

MM/DD/YYYY

MM/DD/YYYY

MM-DD-YYYY

MM-DD-YYYY

缩写日期月份DD HH:MM:SS TimeZone YYYY('Thu Feb 02 20:49:59 MSK 2012')

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

完整记录的日期，日期DD，YYYY HH:MM:SS AM/PM

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

我的要求是将它们全部设置为标准MM/DD/YYYY格式或为null.有什么想法吗?

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

谢谢.

推荐答案

我建议使用带有regexp_like条件的case语句来检测可能的格式，并在then子句中使用适当的日期掩码来返回日期，例如:

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.

这篇关于在Oracle 11g中将多个日期varchar2转换为日期格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！