Oracle 中的有效日期检查

Valid Date Checks in Oracle

提问by vvekselva

I have a date value (either valid date or invalid date) store in varchar format. Is it possible to check the date is valid or not in sql query.

我有一个 varchar 格式的日期值（有效日期或无效日期）存储。是否可以在 sql 查询中检查日期是否有效。

回答by Florin Ghita

Yes, if you know the format and with little plsql.

是的，如果您知道格式并且很少使用 plsql。

Let's say you have the date in format 'yyyy-mon-dd hh24:mi:ss'.

假设您的日期格式为 format 'yyyy-mon-dd hh24:mi:ss'。

create function test_date(d varchar2) return varchar2
is
  v_date date;
begin
  select to_date(d,'yyyy-mon-dd hh24:mi:ss') into v_date from dual;
  return 'Valid';
  exception when others then return 'Invalid';
end;

Now you can:

现在你可以：

select your_date_col, test_date(your_date_col)
from your_table;

回答by Canburak Tümer

You can do it in a block like

你可以在一个块中做到这一点

BEGIN
  select TO_DATE(your_date,'YYYYMMDD') from dual; --ADD INTO V_DUMMY IN PLSQL
EXCEPTION WHEN OTHERS THEN dbms_output.put_line('NOT A VALID DATE');
END;

回答by Adrian Huang

Without using block or creating custom functions or procedures, you can use just the select statement below so it returns the true date value parsed from the date string and returns null if the date string cannot be recognized.

不使用块或创建自定义函数或过程，您可以只使用下面的 select 语句，以便它返回从日期字符串解析的真实日期值，如果无法识别日期字符串，则返回 null。

This approach has limitation. Please see notes below.

这种方法有局限性。请参阅下面的注释。

select
  case
    when regexp_substr(DateStr,'^[[:digit:]]{2}-[[:digit:]]{2}-[[:digit:]]{4}$') is not null then
      case
        when to_number(regexp_substr(DateStr,'[^-]+',1,1))<=12 and to_number(regexp_substr(DateStr,'[^-]+',1,2))<=31
        then add_months(to_date('01-01-1900','MM-DD-YYYY'),(to_number(regexp_substr(DateStr,'[^-]+',1,3))-1900)*12+to_number(regexp_substr(DateStr,'[^-]+',1,1))-1)+to_number(regexp_substr(DateStr,'[^-]+',1,2))-1
        else null
      end
    else null
  end RealDateVal
from MyTable

This example takes date format as "MM-DD-YYYY", and assumes "MyTable" to have the date string column "DateStr". You may have to adjust accordingly for you need.

此示例采用日期格式为“MM-DD-YYYY”，并假定“MyTable”具有日期字符串列“DateStr”。您可能需要根据需要进行相应调整。

The overall approach is to first check the date string to be in the format ??-??-???? where a "?" is a digit, and then check the first ?? to be no larger than 12 (for the month) and the second ?? to be no larger than 31 (for the date). If all pass, then to assembly a date-typed value by using the "add_months()" function to build the year and month, and using the "+" function to build the date. If any of these criteria is not satisfied, then to return null.

总体做法是先检查日期字符串的格式为??-??-???? 哪里有“？” 是一个数字，然后检查第一个？？不大于 12（月份）和第二个 ?? 不大于 31（日期）。如果全部通过，则通过使用“add_months()”函数构建年和月，并使用“+”函数构建日期来组装日期类型的值。如果不满足这些条件中的任何一个，则返回 null。

Note 1: As we have no simple way to check if a year has Feb-29th and I am lazy in checking if a month has the 31st, a day presented as an invalid 29th, 30th or 31st, that would not be caught by the validity check, will be pushed to the next month. (See examples below.) And the query will never return an error.

注意 1：因为我们没有简单的方法来检查一年是否有 2 月 29 日，而且我懒得检查一个月是否有 31 日，一天显示为无效的 29 日、30 日或 31 日，这不会被有效性检查，将推到下个月。（请参阅下面的示例。）并且查询永远不会返回错误。

Note 2: "case when ... is not null then ... else ... end" is lazy, so it won't evaluate the "then ..." part to potentially trigger error, in the case if "when ... is not null" is not satisfied. This feature is fundamental to the overall approach. Instead, the function nvl2() is not lazy and cannot be used instead.

注 2：“case when ... is not null then ... else ... end”是懒惰的，因此它不会评估“then ...”部分以可能触发错误，在这种情况下，如果“when ... ...不为空”不满意。此功能是整个方法的基础。相反，函数 nvl2() 不是惰性的，不能替代使用。

Examples:
'06-15-2015' -> returns valid date
'06-15-2015 ' -> returns null
'06/15/2015' -> returns null
'15-06-2015' -> returns null
'06-31-2015' -> return valid date as 07-01-2015
'02-30-2015' -> return valid date as 03-02-2015

示例：
'06-15-2015' -> 返回有效日期
'06-15-2015' -> 返回 null
'06/15/2015' -> 返回 null
'15-06-2015' -> 返回 null
'06- 31-2015' -> 返回有效日期为 07-01-2015
'02-30-2015' -> 返回有效日期为 03-02-2015

Go further: Depending on how far you want to go, you can mitigate the limitation exhibited in "Note 1" by converting the parsed date value back into string and compare it with the original date string value.

更进一步：根据您想要走多远，您可以通过将解析的日期值转换回字符串并将其与原始日期字符串值进行比较来减轻“注释 1”中显示的限制。

回答by Adrian Huang

And this approach is also a simple select statement without using blocks, custom functions or stored procedures.

而且这种方式也是一个简单的选择语句，不使用块、自定义函数或存储过程。

This example assumes the date format to be "MM-DD-YYYY". It first validates the format being "??-??-????" where "?" is a digit by using regular expression, and then validates the month (the first "??") being not larger than 12, and then finds the last day of that given valid year/month with the last_day() function to validate if the day part (the second "??") to be a valid one for that month. When all are valid, then uses the to_date() function to parse, otherwise returns null.

此示例假定日期格式为“MM-DD-YYYY”。它首先验证格式为“??-??-????” 在哪里 ”？” 是使用正则表达式的数字，然后验证月份（第一个“??”）不大于 12，然后使用 last_day() 函数查找给定有效年/月的最后一天以验证是否一天部分（第二个“??”）是该月的有效部分。当所有都有效时，则使用 to_date() 函数进行解析，否则返回 null。

select
  case
    when regexp_substr(DateStr,'^[[:digit:]]{1,2}-[[:digit:]]{1,2}-[[:digit:]]{4}$') is not null
    then case
      when to_number(regexp_substr(DateStr,'[^-]+',1,1))<=12
      then case
        when to_number(regexp_substr(DateStr,'[^-]+',1,2)) <= extract(day from last_day(add_months(to_date('01-01-1900','MM-DD-YYYY'),(to_number(regexp_substr(DateStr,'[^-]+',1,3))-1900)*12+to_number(regexp_substr(DateStr,'[^-]+',1,1))-1)))
        then to_date(DateStr,'MM-DD-YYYY')
        else null
        end
      else null
      end
    else null
    end InstallDate
from MyTable

回答by byrop

FUNCTION IsDate( p_str IN VARCHAR2, p_format IN VARCHAR2 ) RETURN NUMBER AS
V_date DATE;
BEGIN
    V_Date := TO_DATE( p_str, p_format );
    RETURN 1;
EXCEPTION
   WHEN OTHERS THEN
      RETURN 0;
END;

call:

称呼：

select lastlogin, IsDate(lastlogin,'MM/dd/yyyy  hh24:mi:ss') from users_table

回答by user2001117

Just use to_date function to check wether the date is valid/invalid.

只需使用 to_date 函数来检查日期是否有效/无效。

BEGIN
  select TO_DATE(your_date,DateFormat) from dual;
EXCEPTION WHEN OTHERS THEN dbms_output.put_line('NOT A VALID DATE');
END;