Oracle Date列清理 [英] Oracle Date column cleaning

问题描述

我有一个table1(DueDate varchar2(20)).它具有成千上万种格式不同的日期数据，以及一些不良数据(例如字符).

I have a table1(DueDate varchar2(20)). It has thousands of date data in different format with some bad data like characters.

例如

YYYYMMDD,
MM/DD/YYYY,
MM-DD-YYYY,
M/D/YYYY,
'ABCD'
YYYYMMD,
YYYYMDD,

现在，我不得不推迟逾期一周的日期.我该怎么办?

Now I had to get the dates that are past one week overdue. How do I do that?

谢谢您的帮助.

推荐答案

这是在字符字段中存储日期信息如此糟糕的原因之一.

This is one of the reasons that storing date information in a character field is such a bad idea.

最简单的选择是创建一个函数，该函数尝试使用具有任何优先级顺序的格式(即010203，2003年1月2日或2001年2月3日，或其他)将字符串转换为日期，并捕获异常.像

The easiest option is to create a function that attempts to convert the string to a date using the formats in whatever priority order you have (i.e. is 010203 Jan 2, 2003 or Feb 3, 2001 or something else) and catches the exceptions. Something like

CREATE OR REPLACE FUNCTION my_to_date( p_str IN VARCHAR2 )
  RETURN DATE
IS
  l_date DATE;
BEGIN
  l_date := to_date( p_str, 'YYYYMMDD' );
  RETURN l_date;
EXCEPTION
  WHEN others THEN
    BEGIN
      l_date := to_date( p_str, 'MM/DD/YYYY' );
      RETURN l_date;
    EXCEPTION 
      WHEN others
      THEN
        RETURN null;
    END;
END;

其工作原理类似于

SQL> CREATE OR REPLACE FUNCTION my_to_date( p_str IN VARCHAR2 )
  2    RETURN DATE
  3  IS
  4    l_date DATE;
  5  BEGIN
  6    l_date := to_date( p_str, 'YYYYMMDD' );
  7    RETURN l_date;
  8  EXCEPTION
  9    WHEN others THEN
 10      BEGIN
 11        l_date := to_date( p_str, 'MM/DD/YYYY' );
 12        RETURN l_date;
 13      EXCEPTION
 14        WHEN others
 15        THEN
 16          RETURN null;
 17      END;
 18  END;
 19  /

Function created.

SQL> select my_to_date( '19000101' ) from dual;

MY_TO_DAT
---------
01-JAN-00

SQL> ed
Wrote file afiedt.buf

  1* select my_to_date( '01/02/2005' ) from dual
SQL> /

MY_TO_DAT
---------
02-JAN-05

当然，您必须在代码中编码所有有效的日期格式，我只是处理列表中的前两个.

Of course, you'd have to code the full set of valid date formats in your code, I'm just handling the first two in your list.

这篇关于Oracle Date列清理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！