oracle PL/SQL 检查日期是否有效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14524448/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-19 01:24:14 来源:igfitidea点击:
PL/SQL check date is valid
提问by Jordan Borisov
If I have a date format DDMMin PL/SQL and I want to validate it. What is the correct way to do it?
如果我DDMM在 PL/SQL 中有一个日期格式并且我想验证它。正确的做法是什么？
DDis the day and MMis the moth.
DD是天，MM是蛾。
For an example:
0208 - is a valid date
3209 - is not a valid date
0113 - is not a valid date.
回答by Nick Krasnov
You could write a function like this one, for instance:
你可以写一个这样的函数，例如：
create or replace function is_valid(p_val in varchar2)
return number
is
not_a_valid_day exception;
not_a_valid_month exception;
pragma exception_init(not_a_valid_day, -1847);
pragma exception_init(not_a_valid_month, -1843);
l_date date;
begin
l_date := to_date(p_val, 'ddmm');
return 1;
exception
when not_a_valid_day or not_a_valid_month
then return 0;
end;
SQL> with test_dates(dt) as(
2 select '0208' from dual union all
3 select '3209' from dual union all
4 select '0113' from dual
5 )
6 select dt, is_valid(dt) as valid
7 from test_dates
8 /
DT VALID
---- ----------
0208 1
3209 0
0113 0
回答by Xophmeister
to_dateraises an exception if its input parameter is not a valid date. So you can do something like:
to_date如果其输入参数不是有效日期，则引发异常。因此，您可以执行以下操作：
declare
x date;
begin
x := to_date('3210', 'DDMM'); -- Will raise ORA-1847
x := to_date('0113', 'DDMM'); -- Will raise ORA-1843
exception
when others then
if sqlcode in (-1843, -1847) then
dbms_output.put_line('Invalid Date!');
null;
else
raise;
end if;
end;
