在 Oracle 中检查 DATE 格式

2021-12-30 00:00:00 sql oracle11g oracle

我在一个过程中有以下 SELECT 查询.我需要为 SUBMTD_SRVC_DATE 列添加另一个验证检查.如果 SUBMTD_SRVC_DATE 的值是MMDDYYYY"以外的任何格式，那么我需要填充00000000".假设，如果 SUBMTD_SRVC_DATE = '100515'，那么我需要自动填充 '00000000'.

I have below SELECT query in a procedure. I need to add another validation check for SUBMTD_SRVC_DATE column. If the value of SUBMTD_SRVC_DATE is in any format other than 'MMDDYYYY', then I need to populate '00000000'. Suppose, if SUBMTD_SRVC_DATE = '100515', then I need to automatically populate '00000000'.

任何人都可以请.提前致谢.

Could anyone please on that. Thanks in advance.

SELECT CASE WHEN SMS.SRVC_UNITS IS NULL OR SMS.SRVC_UNITS = -8888 OR SMS.SRVC_UNITS = -9999 THEN '0000000' ELSE LPAD( SMS.SRVC_UNITS , 7 , '0') END
                    || CASE WHEN ( SMS.SUBMTD_SRVC_DATE IS NULL OR UPPER(SMS.SUBMTD_SRVC_DATE) = 'NOT AVAILABLE') THEN '00000000' ELSE SMS.SUBMTD_SRVC_DATE END  AS FILE_VAL
FROM SUBMTD_MED_SRVC SMS

推荐答案

创建用户自定义函数，尝试解析日期；如果有任何异常，则捕获它们并返回您的默认字符串.

Create a user-defined function and try to parse the date; if there are any exceptions then capture them and return your default string.

CREATE FUNCTION check_Date (
  datestring    VARCHAR2,
  format_mask   VARCHAR2 := 'FXMMDDYYYY',
  default_value VARCHAR2 := '00000000'
) RETURN VARCHAR2 DETERMINISTIC
IS
  INVALID_DATE EXCEPTION;
  PRAGMA EXCEPTION_INIT( INVALID_DATE, -20001 );

  p_date DATE;
BEGIN
  IF datestring IS NULL THEN
    RAISE INVALID_DATE;
  END IF;

  p_date := TO_DATE( datestring, format_mask );

  RETURN datestring;
EXCEPTION
  WHEN OTHERS THEN
    RETURN default_value;
END check_Date;
/

替代方案:

SELECT CASE
       WHEN NOT REGEXP_LIKE( datestring, '^(0[1-9]|1[12])(0[1-9]|[12]d|3[01])d{4}$' )
       THEN '00000000'
       WHEN TO_CHAR(
              ADD_MONTHS(
                TRUNC( TO_DATE( SUBSTR( datestring, 5 ), 'YYYY' ), 'YYYY' ),
                TO_NUMBER( SUBSTR( datestring, 1, 2 ) ) - 1
              )
              + TO_NUMBER( SUBSTR( datestring, 3, 2 ) ) - 1,
              'MMDDYYYY'
            )
            <> datestring
       THEN '00000000'
       ELSE datestring
       END
FROM   your_table

相关文章