将VARCHAR2转换为Date，然后再次将其转换为特定的Date格式

Question

我们有一个旧表，其中日期字段以多种格式存储在VARCHAR2中。现在我们计划：

1） 将这些数据存储在具有适当日期字段的视图中。

2） 还以单一一致的格式（如MM/DD/YYYY）存储这些内容。

这是为#1所做的：创建一个函数，以便它可以解析VARCHAR2并将其转换为不同格式的DATE，例如：

IF dataTobeConverted IS NOT NULL
        THEN
            IF substr(dataTobeConverted,3,1) = '-'
             THEN retDate:= TO_DATE(dataTobeConverted,'DD-MON-YYYY');
            END IF;

            IF substr(dataTobeConverted,3,1) = '/'
             THEN retDate:= TO_DATE(dataTobeConverted,'MM/DD/YYYY');
            END IF;
        END IF;

RETURN retDate;

对于#2，我们仍然需要将上述两个日期转换为MM/DD/YYYY，其中一个已采用此格式，因此我们需要先转换另一个（“DD-MON-YYYY”），如果条件存储在视图日期字段的MM/DD/YYYY中。所以现在我们需要将日期转换为日期（从一种格式转换为另一种格式并返回日期）

提前感谢！

Answer 1

正如PrzemyslawKruglej正确指出的那样，您不应该将日期存储在varchar2中。这表示：

alter table oldtable add new_date date;

update oldtable set new_date =
  case
    when substr(old_date,3,1) = '-' then to_date(old_date,'DD-MON-YYYY')
    when substr(old_date,3,1) = '/' then to_date(old_date,'MM/DD/YYYY')
    else null
  end
where old_date is not null;

检查是否已识别每个日期：

select * from oldtable where old_date is not null and new_date is null;

最后：

alter table oldtable drop old_date;

Answer 2

如果我理解正确，就不必再进行转换-如果将字符串转换为日期数据类型，则无论原始日期（例如，VARCHAR2数据类型）看起来如何，它都以相同的方式存储。

向用户显示数据的方式取决于使用TO_CHAR函数指定的会话参数或模型格式。但是，在内部，DATE存储时没有格式模型。

Answer 3

将这些数据存储在视图中

View不存储实际数据-它只是存储的select语句，仅此而已。

第一个if条件要存储在视图日期字段中的MM/DD/YYYY中

当您使用特定格式将表示日期的字符串文字转换为日期数据类型的值时，并不意味着Oracle将转换后的值存储为该格式。为TO\u DATE（）函数提供格式掩码作为第二个参数，只需通知oracle为日期数据类型的值提供适当的内部表示。日期不会以任何特定格式存储。这意味着，一旦您将字符串文字转换为日期数据类型的值，您只需使用to\u char（）函数以您喜欢的任何格式显示该日期，或使用nls\u DATE\u format设置的日期表示格式掩码：

SQL> with t1(col) as(
  2    select to_date('21-10-2013', 'dd-mm-yyyy') from dual union all
  3    select to_date('21/10/2013', 'dd/mm/yyyy') from dual union all
  4    select to_date('21.10.2013', 'dd.mm.yyyy') from dual
  5  )
  6  select to_char(col, 'dd/mm/yyyy') as res
  7    from t1
  8  ;


RES
----------
21/10/2013
21/10/2013
21/10/2013