skip to content »

Validating date format

Sure you can do ALL of that in'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 .

validating date format-23validating date format-53

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. Thanks for your Help Srini create or replace function my2date( p_str in varchar2 ) return date 2 is 3 begin 4 return to_date( p_str ); 5 exception 6 when others then 7 return null; 8 end; 9 /Function created.And then in sqlldr, use:....date_field "my2date(:date_field)",.... select to_date('01-JAN-05') from dual; TO_DATE('---------01-JAN-051 row selected.q=Regular expression for validating Date format dd/MM/yyyy&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a Hi Rameez, Checkout Patterns.aspx?How could you restrict a list of cells to only allow entering date format in Excel worksheet? \d)$or if you are loading a table, consider using DML error logging to reject the bad dates. v=8WV15Bz QIto for an tutorial simply and use a function. Actually In the procedure the date format is validated in the update statement .

Hi, I requirement a requirement to validate date format. I have to check whether given date is in (MM/DD/YYY) format.

"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.

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.

If you are at the conference, drop into the Groundbreaker area and say Hello.