Select Invalid Date

Dev Shed Forums Sponsor:

Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here!

September 2nd, 2004, 06:32 PM

svvc

Registered User

Join Date: Aug 2004

Posts: 12

Time spent in forums: 1 h 20 m 58 sec
Reputation Power: 0

Select Invalid Date

date value is stored in a varchar Column in a table.
I would like to get the rows with invalid dates.

Say the data is like this.
col1 varchar_date
1 2004-03-04
2 2005-00-09
3 2002-12-21
4 1978-12-00

My query should return the 2 and 4.
I tried something like this
select col1 from table where decode( to_date(varchar_date,'yyyy-mm-dd'),value_error,'f','T')='f';

Please help me
Thanks
Sachin

September 3rd, 2004, 03:51 PM

jim mcnamara

......@.........

Join Date: Jun 2004

Posts: 1,307

Time spent in forums: 1 Week 3 Days 4 h 28 m 57 sec
Reputation Power: 48

offhand I can't think of anything except to use PL/SQL - this is a simplfied sample.

Code:

set serverout on size 100000
DECLARE
  
  dt    VARCHAR2(15):=NULL;
  counter NUMBER(10):=0;
CURSOR main IS
    SELECT ROWID, FLD1 from TEST;


FUNCTION Isdate( Tstdate IN VARCHAR2)
RETURN NUMBER
IS  
  
	Tmp  DATE:=NULL;
BEGIN
  SELECT TO_DATE(Tstdate,'YYYY-MM-DD')
  INTO 
       Tmp
  FROM dual; 
  
RETURN 1;
EXCEPTION 
  WHEN OTHERS THEN 
         RETURN 0;    
END;   

BEGIN
    DBMS_OUTPUT.ENABLE(1000000);
    FOR x IN MAIN
    LOOP                
    
        counter:=counter+1;
        dt:=x.fld1;
        IF isdate(dt)=0
        THEN
           dbms_output.PUT_LINE( counter || ' ' || dt);
        END IF;
        
    END LOOP;
        
END;
/

September 3rd, 2004, 05:05 PM

KK2796

Registered User

Join Date: Jul 2004

Posts: 11

Time spent in forums: 50 m 40 sec
Reputation Power: 0

Quote:

Originally Posted by svvc

Your only recourse is PL/SQL, or doing the "dirty work" of writing an SQL function which can distinguish between a date and a non-date...
something like

Code:

SELECT * FROM TABLE WHERE 
 CASE WHEN LENGTH(COL) = 10 THEN
  CASE WHEN TRANSLATE(SUBSTR(COL,1,4),'0123456789','NNNNNNNNNN') = 'NNNN' THEN
   CASE WHEN TO_NUMBER(SUBSTR(COL,1,4)) BETWEEN 1900 AND 2100 THEN
    CASE WHEN SUBSTR(COL,5,1) = '-' THEN
     CASE WHEN TRANSLATE(SUBSTR(COL,6,2),'0123456789','NNNNNNNNNN') = 'NN' THEN
      CASE WHEN TO_NUMBER(SUBSTR(COL,6,2)) BETWEEN 1 AND 12 THEN
       CASE WHEN SUBSTR(COL,8,1) = '-' THEN
        CASE WHEN TRANSLATE(SUBSTR(COL,9,2),'0123456789','NNNNNNNNNN') = 'NN' THEN
         CASE WHEN TO_NUMBER(SUBSTR(COL,9,2)) BETWEEN 1 AND 31 THEN
          'Y'
         END END END END END END END END END 
 IS NULL

Of course, this will pull rows where the date field is *syntactically* incorrect... you'd have to do a bit more work to pull semantically incorrect dates, such as April 31 or February 29th on non-leap-years...

editted to add:

Ah hell, I can't post a cs101 problem without solving it myself... here's the code:

Code:

SELECT * FROM TABLE WHERE 
 CASE WHEN LENGTH(COL) = 10 THEN
  CASE WHEN TRANSLATE(SUBSTR(COL,1,4),'0123456789','NNNNNNNNNN') = 'NNNN' THEN
   CASE WHEN TO_NUMBER(SUBSTR(COL,1,4)) BETWEEN 1900 AND 2100 THEN
    CASE WHEN SUBSTR(COL,5,1) = '-' THEN
     CASE WHEN TRANSLATE(SUBSTR(COL,6,2),'0123456789','NNNNNNNNNN') = 'NN' THEN
      CASE WHEN TO_NUMBER(SUBSTR(COL,6,2)) BETWEEN 1 AND 12 THEN
       CASE WHEN SUBSTR(COL,8,1) = '-' THEN
        CASE WHEN TRANSLATE(SUBSTR(COL,9,2),'0123456789','NNNNNNNNNN') = 'NN' THEN
         CASE WHEN TO_NUMBER(SUBSTR(COL,9,2)) BETWEEN 1 AND 31 THEN
          CASE WHEN SUBSTR(COL,9,2) <> '31' 
                 OR SUBSTR(COL,6,2) IN ('01','03','05','07','08','10','12') THEN
           CASE WHEN SUBSTR(COL,9,2) <> '30' 
                  OR SUBSTR(COL,6,2) NOT IN ('02') THEN
            CASE WHEN SUBSTR(COL,6,2) <> '02' 
                   OR SUBSTR(COL,9,2) NOT IN ('29') 
                   OR (MOD(TO_NUMBER(SUBSTR (COL,1,4)),4) = 0 
                       AND 
                      (MOD(TO_NUMBER(SUBSTR (COL,1,4)),100) <> 0
                          OR MOD(TO_NUMBER(SUBSTR (COL,1,4)),1000) = 0))
                   THEN
          'Y'
         END END END END END END END END END END END END
 IS NULL

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: