Validating date in oracle

The function TO_DATE can determine if a string represents a valid date.

validating date in oracle-55

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.

Thanks again :) May 06, 2016 - am UTC "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 ." There's a name for that ...

This example assumes the date format to be "MM-DD-YYYY".

The ISDATE program determines whether a text expression represents a valid date.

Next year, I may be asked "Does this VARCHAR2 column represent a date, and, if so, is that date in the future? Reusability is also the reason I made the format mask an argument to to_dt: next year I may have to deal with dates in 'MM/DD/YYYY' format.

I want users to be able to enter a date format string so they can specify how they want a date value to be displayed/entered.

The function returns select validate_conversion('test123' as number) test1 from dual;--0 select validate_conversion(300 as number) test2 from dual;--1 select validate_conversion('01012017' as date,'mmddyyyy') test3 from dual;--1 select validate_conversion('31012017' as date,'mmddyyyy') test4 from dual;--0 select validate_conversion('31-jan-2017' as date,'dd-mon-yyyy') test5 from dual;--1SELECT * FROM hr.employees emp WHERE VALIDATE_CONVERSION(emp.employee_id AS NUMBER) = 1; SELECT VALIDATE_CONVERSION(emp.employee_id AS NUMBER) emp_id_test, employee_id FROM hr.employees emp; SELECT DECODE(VALIDATE_CONVERSION(emp.employee_id AS NUMBER),0, 'NOT NUMBER',1,'NUMBER','N/A') emp_id_test,employee_id FROM hr.employees emp; SELECT case when VALIDATE_CONVERSION(emp.employee_id AS NUMBER) = 1 then emp.employee_id else -1 end emp_id_test, employee_id FROM hr.employees emp; This feature, which comes with Oracle 12c R2, will reduce the complexity of the code we write for data validation in SQL and PL/SQL code blocks and improve our application performance.

Native code support for data conversion is a significant development in this context.

If any of these criteria is not satisfied, then to return null. end" is lazy, so it won't evaluate the "then ..." part to potentially trigger error, in the case if "when ... This feature is fundamental to the overall approach.

to be no larger than 12 (for the month) and the second ?? If all pass, then to assembly a date-typed value by using the "add_months()" function to build the year and month, and using the " " function to build the date.

Why, then, did I choose to make the to_dt function return a DATE instead of a VARCHAR2?

Tags: , ,