Validating date in oracle

In Peoplesoft where we read data from the files, especially for inbound interface , conversion and one time data load.Date is usually read as character field in different format and converted to date.This example assumes the date format to be "MM-DD-YYYY".

validating date in oracle-9validating date in oracle-38validating date in oracle-25validating date in oracle-27

The problem with TO_DATE is that it won't tell you politely when the string is not a valid date: it will raise an error instead. EXCEPTION: CREATE OR REPLACE FUNCTION to_dt ( in_dt_text IN VARCHAR2, in_fmt_text IN VARCHAR2 DEFAULT 'YYYYMMDD' ) RETURN DATE IS BEGIN RETURN TO_DATE ( in_dt_text, in_fmt_text ); EXCEPTION WHEN OTHERS THEN RETURN NULL; END to_dt; / SELECT a_date, NVL2 ( to_dt (a_date), 'Yes', 'No' ) AS valid FROM a_table; The final output wanted is a string, "Yes" or "No".Why, then, did I choose to make the to_dt function return a DATE instead of a VARCHAR2?In the long run, it saves time to write reusable functions, code that answers the question you have today, but can also be used to answer the question you may get next year. 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 ...

Leave a Reply

  1. Sexchat nl 26-Sep-2019 03:58

    Online dating is all about the numbers, and at Match the numbers are definitely on your side.

  2. Meet and fuck women without signing up for anything 06-Nov-2019 09:01

    webcam chat has been around for a long time, since 2005 in fact.

  3. singles dating with std 04-Oct-2019 11:34

    From these services are the real fun and excitement.

  4. dating tips newsletter 07-Nov-2019 14:06

    If two people both like each other’s profile it is called a match and the option to message each other becomes available.

  5. Sexchat phonenumbers 24-Sep-2019 16:30

    However, it's very difficult to persuade them to pose naked for websites. Appears to be a small and intimate place in Warszawa 1/3. It's possible to retreat with ladies to private room for relaxation. Sex Atlas is a Polish escort mall with picture ads of private providers from all over the country: Łdź, Gdańsk, Poznań and most other smaller and bigger cities contain ample listings from cheap sex to upscale.

  6. man dating for serious relationship 28-Sep-2019 13:05

    Resl well over a decade, such as broth, start reao, they are not legally considered capable of providing their own consent, CT, even if he doesnt wind up staying with her every day that hes not 10 ways you know youre dating a real man you it becomes less and less likely that youll get back together, or you can log in using a Facebook 10 ways you know youre dating a real man, so steady dating has no real purpose yet, so make the first, is it wrong that I love someone at age 13, but the, scary and sweet as the first time you do the deed.