检查字符串是否是有效日期 [英] Check String of characters is a valid date or not

问题描述

我有一个表，该表的字段sourcefilename有5条记录.以下是记录.

I have a table with field sourcefilename which has 5 records. Following are the records.

SN. SOURCEFILENAME
1. 20170215095453_1.Iredell Memorial Hospital Dental Eligibility.xls_INFREPT01.txt
2. Iredell Memorial Hospital Eligibility April 2017.xls_INFREPT01.txt
3. Iredell Memorial Hospital Eligibility March 2017.xls_INFREPT01.txt
4. Iredell Memorial Hospital Eligibility May 2017.xls_INFREPT01.txt
5. Iredell Memorial Hospital October 2016 Dental Eligibility.xls_EligData.txt

我只需要提取前8个字符并检查其有效日期.如果日期有效，则返回TRUE，否则返回FALSE.

I just need to extract first 8 characters and check if its a valid date. If it is a valid date then return TRUE else FALSE.

我尝试了ISDATE函数.还有其他选择吗?

I tried ISDATE function. Is there are any other alternatives?

SELECT DISTINCT SubStr(sourcefilename,1,8),
CASE WHEN isdate(SubStr(sourcefilename,1,8),'YYYYMMDD') = 1 THEN 'TRUE' ELSE 'FALSE' END  FROM ai_4451_1_metl;

推荐答案

Oracle 12.2提供了一个新功能

Oracle 12.2 provides a new function VALIDATE_CONVERSION. So far I never used it, but I assume it would be like this:

CASE VALIDATE_CONVERSION(SubStr(sourcefilename,1,8) AS DATE, 'YYYYMMDD') 
   WHEN 1 THEN 'TRUE'
   ELSE 'FALSE'
END

这篇关于检查字符串是否是有效日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！