在不使用函数的情况下在Oracle中验证日期

问题描述：

我是Oracle的新手.我必须在oracle数据库的表中的列中选择无效的日期.但由于在数据库中具有只读权限，因此我也无法编写函数. 任何人都可以帮助编写简单的查询以选择列中的无效日期吗?

I am newbie in Oracle. I have to select invalid dates in column in table in oracle database. But I also can't write function because of having read only rights in database. Can anybody help to write simple query to select invalid date in columns:

例如从表格中选择日期，to_date(日期"yyyy/mm/dd")

e.g. select dates, to_date(dates 'yyyy/mm/dd') from table

在上面的查询中，如果日期的格式无效，则会出现错误.但是我必须输出该日期而不是错误.我们可以通过简单的查询来做到吗?

In the above query if the date is not in valid format it give error. But instead of error I have to output that date. Can we do it in simple query?

答

您可以使用正则表达式测试格式.

You can test the format using a regular expression.

那会是这样的:

select dates
from tbl
where regexp_like(dates, '[[:digit:]]{4}/[[:digit:]]{2}/[[:digit:]]{2}')

这行得通.它检查格式是否为"4位数字/2位数字/2位数字".您可能想要更强的东西，例如:

This works okay. It checks that the format is in "4-digit number / 2 digit number / 2 digit number". You might want something stronger, such as:

select dates
from tbl
where regexp_like(dates, '[[:digit:]]{4}/[[:digit:]]{2}/[[:digit:]]{2}') or
      (substr(dates, 1, 4) not between '1900' and '2014' or
       substr(dates, 6, 2) not between '01' and '12' 
       substr(dates, 9, 2) not between '01' and '31'
      )

这将检查格式和每列中的合理值.当然，它不会检查6月31日，但是会捕获很多错误.

This checks the format and for reasonable values in each column. Of course, it doesn't check for June 31st, but it will catch many errors.

在不使用函数的情况下在Oracle中验证日期

相关推荐