Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Puerto Rico
    Posts
    69

    Angry Unanswered: Ignore non date fields.

    I have an oracle Database with an string column that users enters dates as strings. I know it isn't the best approach but it's done by a software and I can't change that (Infinity QS descriptor). I made a view that uses that column and changes it to a date for further processing but as you might know there are also mistakes entered there and the view obviously rises an error message. Here's the part where I try to flter those errors out:

    Code:
    to_date(
    (SELECT substr(A.F_NAME,1,10) FROM DESC_DAT A, 
    DESC_GRP B, SGRP_INF C, SGRP_DSC D WHERE A.F_DSGP = B.F_DSGP 
    AND B.F_NAME= 'INSPECTION DATE (MM/DD/YYYY)' 
    AND C.F_SGRP = D.F_SGRP AND  A.F_DESC = D.F_DESC 
    AND C.F_SGRP = SGRP_INF.F_SGRP AND ROWNUM<2 AND A.F_DSBL =0 
    and a.f_name is not null and LENGTH(a.f_name)>=10),'MM/DD/YYYY' ) 
    AS TIME_TOOK_PLACE
    The fiedl is F_name and I try the substring part to avoid longer than the format strings (MM/DD/YYYY). The problem comes when the string is shorter. Is there a way to avoid including non dates in the view? Like select *If not a date* from ... I looked around for isdate() function or something but no luck yet.

    Any idea is welcomed.
    Last edited by javydreamercsw; 12-19-05 at 08:49.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    Create your own function!
    Kinda like this:
    Code:
    Create Or Replace Function Set_Date 
    (Txt_In Varchar2)
    Return Date Is
    L_Dt Date;
    Begin
      L_Dt:=To_Date(Txt_In,'MM/DD/YYYY');
      Return L_Dt;
    Exception
      When Others Then
        Return Null;
    End;
    /

    Last edited by LKBrwn_DBA; 12-19-05 at 11:05.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •