Thread: Ignore non date fields.
12-19-05, 07:46 #1Registered User
- Join Date
- Feb 2004
- Puerto Rico
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:
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
Any idea is welcomed.
Last edited by javydreamercsw; 12-19-05 at 07:49.
12-19-05, 09:58 #2Registered User
- Join Date
- Jun 2003
- West Palm Beach, FL
Create your own function!
Kinda like this:
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 10:05.The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb