資料庫黑洞

每次要導入系統時，都要先幫客戶做資料移轉，格式需要特別小心，特別是日期的民國年，要轉成西元年在寫入

假設我們取得的資料是 '104.01.02' (民國年，3碼數字 + '.' + 2碼數字 + '.' + 2碼數字)

我們可以如下處理

CREATE TABLE TRAN_A(
  COL_DATE VARCHAR2(50)
);
CREATE TABLE TRAN_B(
  COL_DATE DATE
);

--寫入移轉日期(民國年，3碼數字 + '.' + 2碼數字 + '.' + 2碼數字)
INSERT INTO TRAN_A VALUES ('104.01.02');

--去除 '.': 1040102
SELECT REPLACE(COL_DATE, '.', '') AS "COL_DATE" FROM TRAN_A;

--轉西元年: 20150102
SELECT TO_NUMBER(REPLACE(COL_DATE, '.', '')) + 19110000 AS "COL_DATE" FROM TRAN_A;

--轉日期格式: 02-JAN-15
SELECT TO_DATE(TO_CHAR(TO_NUMBER(REPLACE(COL_DATE, '.', '')) + 19110000), 'yyyy/mm/dd') AS "COL_DATE" FROM TRAN_A;

--寫入
INSERT INTO TRAN_B
SELECT TO_DATE(TO_CHAR(TO_NUMBER(REPLACE(COL_DATE, '.', '')) + 19110000), 'yyyy/mm/dd') FROM TRAN_A;

 

查詢結果

 

 

然而，客戶給的資料，總不會這麼剛好都是統一格式的，例如資料裡面可能月或日沒有補 0 ('104.1.2')，就會造成我們日期轉換出錯，比較保險的作法是先判別 2 個點的位置，再用切割取字的方式去補 0，組成我們要的格式後，再轉為日期。

 

範例語法如下:

INSERT INTO TRAN_A(COL_DATE) VALUES ('104.12.2');
INSERT INTO TRAN_A(COL_DATE) VALUES ('104.2.21');

--日期格式轉換出錯，ORA-01861: literal does not match format string
SELECT TO_DATE(TO_CHAR(TO_NUMBER(REPLACE(COL_DATE, '.', '')) + 19110000), 'yyyy/mm/dd') AS "COL_DATE" FROM TRAN_A;

 

我的解法是多建立兩個欄位，分別記錄 '.' 的位置，再幫日期做補 0 的動作

ALTER TABLE TRAN_A ADD DOT_YEAR NUMBER;
ALTER TABLE TRAN_A ADD DOT_MONTH NUMBER;

--更新 '.' 位置
UPDATE TRAN_A SET DOT_YEAR = INSTR(COL_DATE, '.'), DOT_MONTH = INSTR(COL_DATE, '.', INSTR(COL_DATE, '.') + 1);

--不足2位的月.日補0，並去除 '.'
--104.1.2 => 1040102
UPDATE TRAN_A SET COL_DATE = SUBSTR(COL_DATE, 1, DOT_YEAR - 1) || LPAD(SUBSTR(COL_DATE, DOT_YEAR + 1, DOT_MONTH - DOT_YEAR - 1), 2, '0') || LPAD(SUBSTR(COL_DATE, DOT_MONTH + 1), 2, '0');

--寫入 TRAN_B，正常
INSERT INTO TRAN_B
SELECT TO_DATE(TO_CHAR(TO_NUMBER(COL_DATE) + 19110000), 'yyyy/mm/dd') FROM TRAN_A;

 

執行結果

 

本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際 授權條款授權，文章歡迎轉載，請註明出處，謝謝~~~