11 Replies Latest reply on Jul 24, 2009 12:03 PM by 710061

    how to check for valid date ?

    710061
      Hi ,

      I have a stored procedure which has an input parameter of varchar datatype. Actually this parameter is a date , now i want to check whether this parameter is a valid date or not. My oracle version is 10g . I tried ISDATE() inbuilt function but my oracle version does not support it.

      Can you ppl please help how to do it ?

      Regards

      Shobhit
        • 1. Re: how to check for valid date ?
          Danish
          Hello Shobhit,
          Below is the example code which you can do for Date Check. Create a function for this, hope it will help you
          CREATE OR REPLACE FUNCTION F_DATE(v_date IN VARCHAR2) RETURN NUMBER IS
              v_date1 DATE;
          BEGIN
              select to_date(v_date) into v_date1 from dual;
                  RETURN 1;
              Exception WHEN Others THEN
                  RETURN 0;
          END;
          ----------------------------------------------    
          SELECT F_DATE('01-JAN-09') FROM DUAL;
          -- Returns 1
          SELECT F_DATE('111111') FROM DUAL;
          -- Returns 0
          please mark if it help you or correct
          Regards,
          Danish
          • 2. Re: how to check for valid date ?
            664787
            Hi Shobhit,

            What is the purpose of this varchar data type? If it is a date, why not create it to be date datatype.
            Alternatively, you can always use your varchar as date data type.
            What is the format of date you need?
            You can use to_char and to_Date functions to manipulate your varchar data type and treat it like date.

            Regards,
            Navnit
            • 3. Re: how to check for valid date ?
              618632
              you can try:

              create or replace your_prodecure_name ( p_date in varchar2 ) is

              l_date date;

              begin --> begin own exception handler
              l_date := to_date ( p_date, 'dd.mm.yyyy' ); --> use your date-format
              exception
              when others
              then raise_application_error ( -20000, 'Not a valid date in your_prodecure_name.' );
              end; --> end own exception handler -- you have then to catch this exception in your forms-code

              begin
              your_code; ...
              end;
              • 4. Re: how to check for valid date ?
                Jan Hansen
                Try something like this:

                create function isdate(InTxt in varchar2) return boolean is
                dummy date;
                begin
                dummy := to_date(InTxt, 'dd-mm-yyyy');
                return true;
                exception
                when value_error then
                return false;
                end;
                • 5. Re: how to check for valid date ?
                  710061
                  hi....


                  I tried the exact same function....but when i am using the function and the date is not in a correct format then it is giving me an error saying "not a valid month"
                  • 6. Re: how to check for valid date ?
                    710061
                    hi navnit punj,

                    I am making an application where the user is bulk uploading excel files. Now in one of the columns of the excel file suppose to be a date . But if the user makes a mistake of typing some wrong value then it will go to an error record table prompting him to reupload after rectifying the errors.

                    Did you understand now y i need it ?

                    Shobhit
                    • 7. Re: how to check for valid date ?
                      710061
                      hi janhans,

                      I used this function but still i am getting the same error. When the date is a valid date then the function returns 1 buttt when the date is not valid then it gives me an error saying not a valid month. the exception is not getting handled .

                      I am using the function like this :

                      select isdate1('08-13-2009') from dual; (this statement is giving me an error not returning 0).

                      Tell me if i am doing this correct
                      • 8. Re: how to check for valid date ?
                        Danish
                        Hello,
                        SELECT F_DATE('08-13-2009') FROM DUAL;
                        
                        ------Return 0 in my solution
                        You have to pass a VARCHAR value in F_DATE funtion as you can see above this as
                        F_DATE(v_date IN VARCHAR2)
                        here v_date is an input parameter with datatype VARCHAR.
                        IF you write DATE data type here then you will get error.

                        Regards,
                        Danish
                        • 9. Re: how to check for valid date ?
                          710061
                          yeah danish....now its working for me too once i replaced the F_DATE function exactly the same way told here.....

                          let me just check it using it in my stored procs as well..


                          Thanks

                          Shobhit
                          • 10. Re: how to check for valid date ?
                            Danish
                            Hi Shobhit,

                            If it is working then please mark the answer as CORRECT. ;)

                            Regards,
                            Danish
                            • 11. Re: how to check for valid date ?
                              710061
                              yeah sure...danish....here u go...!!!