我有一個存盤日期的 col，但它是 8 位數字格式（例如 20220613）我如何拆分它并檢查條件？
- 對于前 4 位數字，我需要驗證它是否早于當年，
- 對于中間 2 位數字，我需要驗證它是否在 01 - 12 之間，
- 對于最后 2 位數字，我需要以月份為基礎，它是否在 01 - 31 之間（例如，如果中間 2 位數字（月份）是 06，則最后 2 位數字不能大于 31）
我可以知道如何在 oracle sql 查詢中撰寫此邏輯嗎？謝謝。??
uj5u.com熱心網友回復：
您應該使用DATE資料型別存盤日期值。
如果你不能（你應該）那么：
CREATE TABLE table_name (col NUMBER(8));
使用觸發器并將值轉換為 aDATE并檢查它是否在之前SYSDATE：
CREATE TRIGGER table_name__col__trg
BEFORE INSERT OR UPDATE OF col ON table_name
FOR EACH ROW
BEGIN
IF TO_DATE(:NEW.col, 'YYYYMMDD') > SYSDATE THEN
RAISE_APPLICATION_ERROR(-20000, 'Future date not allowed.');
END IF;
END;
/
適用于：
INSERT INTO table_name (col) VALUES (TO_CHAR(SYSDATE, 'YYYYMMDD'));
INSERT INTO table_name (col) VALUES (20220229);
但對于：
INSERT INTO table_name (col) VALUES (TO_CHAR(SYSDATE 1, 'YYYYMMDD'));
給出例外：
ORA-20000: Future date not allowed.
對于：
INSERT INTO table_name (col) VALUES (20220229);
INSERT INTO table_name (col) VALUES (20200230);
給出例外：
ORA-01839: date not valid for month specified
如果您只想要當前年份之前的日期，那么：
CREATE TRIGGER table_name__col__trg
BEFORE INSERT OR UPDATE OF col ON table_name
FOR EACH ROW
BEGIN
IF TO_DATE(:NEW.col, 'YYYYMMDD') >= TRUNC(SYSDATE, 'YY') THEN
RAISE_APPLICATION_ERROR(-20000, 'Only dates before this year allowed.');
END IF;
END;
/
如果您希望它作為SELECT查詢而不是對表的約束，那么從 Oracle 12 開始，您可以使用：
SELECT *
FROM table_name
WHERE TO_DATE(col DEFAULT NULL ON CONVERSION ERROR, 'YYYYMMDD') <= SYSDATE
（或者< TRUNC(SYSDATE, 'YY')，如果您想要今年之前的值。）
db<>在這里擺弄
uj5u.com熱心網友回復：
首先將其存盤為日期資料型別。這意味著資料庫理解資料 - 它可以為您驗證它，它知道如果您過濾日期在 2021 年 12 月 31 日和 2022 年 1 月 1 日之間的行，則范圍只有一天而不是 ~10000。
如果您不能這樣做，那么您可以使用validate_conversion檢查資料型別（請記住FX在格式掩碼中使用修飾符以獲得完全匹配）。此函式使用字串輸入，因此請確保您也使用顯式格式掩碼將數字轉換為字串。
舉個例子 ;
SELECT VALIDATE_CONVERSION('20220631' AS DATE,'YYYYMMDD') FROM dual;
將回傳零，表示該值作為日期無效，而
SELECT VALIDATE_CONVERSION('20220613' AS DATE,'YYYYMMDD') FROM dual;
回傳一個表明這是一個有效 的日期值
如果您因為沒有運行最新版本而無法執行此操作，請創建自己的函式，該函式嘗試使用所需的格式掩碼（再次使用FX）進行轉換并處理錯誤。
uj5u.com熱心網友回復：
到目前為止，您最好的選擇是放棄將日期存盤為除DATE資料型別之外的任何其他內容的想法。無論您對當前的模型做什么，都是徒勞無益的。
無論如何，這是您當前問題的一種選擇。
如何拆分該數值？使用substr功能。然后檢查它的每個部分是否正常，最后 - 如果所有部分都正常，那么整個值是正常的。
SQL> select * from test order by id;
ID DATUM
---------- ----------
1 20220613 -- wrong, because year isn't earlier than current year
2 20220230 -- wrong, because February doesn't have 30 days
3 20210524 -- OK
SQL> with temp as
2 (select id, datum,
3 case when substr(datum, 1, 4) < extract(year from sysdate) then 1 else 0 end year_ok,
4 case when substr(datum, 5, 2) between 1 and 12 then 1 else 0 end month_ok,
5 case when substr(datum, -2) between 1 and
6 to_char(last_day(to_date(substr(datum, 1, 6), 'yyyymm')), 'dd') then 1
7 else 0 end day_ok
8 from test
9 )
10 select id, datum,
11 case when year_ok * month_ok * day_ok = 1 then 'OK' else 'Error' end status
12 from temp
13 order by id;
ID DATUM STATUS
---------- ---------- ------
1 20220613 Error
2 20220230 Error
3 20210524 OK
SQL>
uj5u.com熱心網友回復：
將邏輯更改為以下內容：
- 檢查是否是日期。
- 如果是日期，請檢查年份是否<當前年份。
要檢查一個值是否為日期，請使用函式VALIDATE_CONVERSION并從有效值中提取年份，請使用EXTRACT.
把它們放在一起，這給出了：
WITH test_data (dt_as_num) AS
(
SELECT 20210214 FROM DUAL UNION ALL
SELECT 20220613 FROM DUAL UNION ALL
SELECT 20212213 FROM DUAL
)
select dt_as_num ,
CASE WHEN
VALIDATE_CONVERSION(TO_CHAR(dt_as_num) AS DATE, 'YYYYMMDD', 'NLS_DATE_LANGUAGE = American') = 1 THEN
CASE WHEN EXTRACT(YEAR FROM TO_DATE(dt_as_num,'YYYYMMDD')) < EXTRACT(YEAR FROM SYSDATE) THEN 'ok'
ELSE '> curr_year'
END
ELSE 'bad date'
END as result
from test_data;
DT_AS_NUM RESULT
---------- -----------
20210214 ok
20220613 > curr_year
20212213 bad date
但當然，正如其他人所說：日期屬于 type 的列DATE。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/490004.html
上一篇：格式選擇plsql中的輸出資訊