how to check for valid date ?

hi.shobhit76
    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 ?
            Magoo
            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 ?
                hi.shobhit76
                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 ?
                  hi.shobhit76
                  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 ?
                    hi.shobhit76
                    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 ?
                        hi.shobhit76
                        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 ?
                            hi.shobhit76
                            yeah sure...danish....here u go...!!!