Validating date format Webcam chat bot xxx
This question is Hi Tom, I've been using Oracle for Sometime now, I use a lot of MSSQL and Sybase, I'm trying to load some ascii file using SQL Loader(sqlldr), I want to know if there is any oracle function equvivalent to ISDATE() function in MSSQL or Sybase.
If my Ascii file contains data which is not of date datatype, I want to insert a NULL instead of loading some wrong data or getting an error in sqlload.
It will include the date values as (21/01/2013 or 20100101 string with any date format) If I have to validate the string format how it can be done .
As my procedure requires the date format as 'MM/DD/YYYY' and if any other format is passed through input file it will update the error description column as invalid date format . Note - I don't want any function created for the same Thanks in advance..!!!
CREATE OR REPLACE FUNCTION MY2DATE (p_str IN VARCHAR2 ,format_picture IN VARCHAR2) RETURN DATEISBEGIN RETURN TO_DATE(p_str, format_picture); EXCEPTION WHEN OTHERS THEN RETURN NULL; END;/Function Created.
Elapsed: .78 [email protected] tom, I'm using isdate function provided by you (pasted below) ********** create or replace function isdate ( p_string in varchar2, p_fmt in varchar2 := null) return varchar2 as l_date date; begin l_date := to_date(p_string,p_fmt); return 'TRUE'; exception when others then return 'FALSE'; end; ********** But to my surpise it is taking '20092007' and '2007/2007' to be a valid date and converting it to a date but I want them to be reported as error pl refer below SQLHi Tom, I was just wondering if there is an SQL only solution to validate date values? We have a scenario where 1.5 million records in a staging table have to be validated and date value is stored as a VARCHAR2 column in 'YYYYMMDD' format.
Hi all, i am trying to validate a date having "yyyy-MM-ddd" format. my code is validating the date when it is like "07-07-07" to invalid date but when i am trying to mention date like "2007123-07-07" it is accepting the date. "Note - I don't want any function created for the same" - why ? Well, you could use a regular expression to get pretty close:^(? it's called a function :-)If you are on 12c, you can have that function defined *inside* the update statement itself update /* with_plsql */ 2 ( 3 with 4 function date_checker(p_str varchar2) return date is 5 dte date; 6 begin 7 dte := to_date(p_str,'dd/mm/yyyy'); 8 return dte; 9 exception 10 when others then return null; 11 end; 12 function date_error_msg(p_str varchar2) return varchar2 is 13 dte date; 14 begin 15 dte := to_date(p_str,'dd/mm/yyyy'); 16 return 'OK'; 17 exception 18 when others then return 'Fail'; 19 end; 20 select x,d,err, date_checker(x) valid_date, date_error_msg(x) err_text 21 from t 22 ) 23 set d = valid_date, err = err_text; 24 / 2 rows updated. Sure you can do ALL of that in SQL..you're update statement will be 200 lines long....... yes this can be one of the solution but the update query will look so messed up . I was thinking something like using case statement in update and check for converting date using to_date and handle exception in the case block . I know function will make the thing a lot easier but I need to try all the other options before going for a function lets hope something like this works :-Dthis is my first question ever . Then its leap years based on 100 year and 400 year boundaries...