是否可以在日期列中加載各種格式的數據？甲骨文 (is that possible to load data of various format in date column | Oracle)

問題描述

是否可以在日期列中加載各種格式的數據？甲骨文 (is that possible to load data of various format in date column | Oracle)

表：

SQL> DESC EMO_SRC;
 Name                                                  Null?    Type
 ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
 EMPLOYEE_NAME                                                  VARCHAR2(30)
 EMPLOYEE_NUMBER                                                NUMBER
 STATE                                                          VARCHAR2(10)
 ZIP                                                            NUMBER
 DOB                                                            DATE
 AGE                                                            NUMBER
 SEX                                                            VARCHAR2(7)
 MARITALDESC                                                    VARCHAR2(15)
 CITIZENDESC                                                    VARCHAR2(15)
 HISPANIC_LATINO_RACEDESC                                       VARCHAR2(15)
 DATE_OF_HIRE                                                   DATE
 DATE_OF_TERMINATION                                            DATE
 REASON_FOR_TERM                                                VARCHAR2(50)
 EMPLOYMENT_STATUS                                              VARCHAR2(15)
 DEPARTMENT                                                     VARCHAR2(30)
 POSITION                                                       VARCHAR2(20)
 PAY_RATE                                                       NUMBER(6,2)
 MANAGER_NAME                                                   VARCHAR2(20)
 EMPLOYEE_SOURCE                                                VARCHAR2(30)
 PERFORMANCE_SCORE                                              VARCHAR2(40)

表 EMO_SRC 有 3 個“日期”列 DOB,Date_of_hire 和 date_of _Termination

如果

==========================================================
 DOB             | Date_of_hire      | date_of_termination
============================================================
 11/24/1987         10/27/2008            10/28/2016
 4‑26‑1984          1‑06‑2014
 02‑26‑1984         09/29/2014            4‑15‑2017

像上面一樣，數據是隨機的這 3 列的日期格式。Oracle 不允許加載“01‑06‑2014”格式。請讓我知道有什麼方法可以加載不同格式的日期值，或者我需要將數據轉換為 '00/00/0000' 格式。大量數據不匹配是負載。那麼如何更改和加載表中的數據。

SQL> show parameters nls_date_format;

NAME                                 TYPE       VALUE
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
nls_date_format                      string     DD‑MON‑RR

請告訴我解決方案。

參考解法

方法 1:

You didn't mention which tool you use.

Anyway, you have a problem. "Date" formats are different within columns and within rows which makes it even worse.

I'd suggest you to create a function which will accept input values ‑ "dates" (actually, strings) you find in source data and try to convert it to a valid DATE using TO_DATE function with different format masks: mm/dd/yyyy, dd‑mm‑yyyy, etc., whichever you find in source. Use inner BEGIN‑EXCEPTION‑END blocks so that the first failure wouldn't terminate function's execution. If you manage to find correct date value, fine ‑ load it. If not, log the error and try to fix it by another TO_DATE's format mask. Optionally, you might use REGEXP_LIKE to verify input format.

Problem you can't solve is a string that looks like 10‑08‑20. Which is which? Is 10 day, month or year? The same goes for other values.

Also, calling a function for all those values ‑ if source is large ‑ will certainly take a lot of time.

(by Jai Barathi、Littlefoot)

參考文件

1. is that possible to load data of various format in date column | Oracle (CC BY‑SA 2.5/3.0/4.0)