6 Replies Latest reply: Dec 18, 2012 10:25 AM by Lucy Discover RSS

    character conversion error on date comparision in function returning boolea

    Lucy Discover
      Hi all , I get character conversion in the below Code.
      DECLARE 
      finish_time date;
      begin 
      select max(trip.finish_time)  into finish_time    from trip
        WHERE ((trip.operator_counter = :P12_operator_counter));
      if ( to_char(finish_time, 'DD-MON-YYYY HH24:MI') > ( to_char(:P12_start_time, 'DD-MON-YYYY HH24:MI') ))
         then   return false;
      else
      return true;
      end if; 
      end;
        • 1. Re: character conversion error on date comparision in function returning boolea
          TexasApexDeveloper
          Looks like the default date format for your application doesn't match the date conversion string you are using...

          Why not try this:
          select max(trip.finish_time)  
            from trip
          WHERE ((trip.operator_counter = :P12_operator_counter));
          when prompted enter in the value you are expecting the operator_counter form item to have.. and the note the format that your date is returned in.. Then modify the format string in your query to match this..

          Thank you,

          Tony Miller
          Ruckersville, VA
          • 2. Re: character conversion error on date comparision in function returning boolea
            fac586
            Lucy Discover wrote:
            Hi all , I get character conversion in the below Code.
            DECLARE 
            finish_time date;
            begin 
            select max(trip.finish_time)  into finish_time    from trip
            WHERE ((trip.operator_counter = :P12_operator_counter));
            if ( to_char(finish_time, 'DD-MON-YYYY HH24:MI') > ( to_char(:P12_start_time, 'DD-MON-YYYY HH24:MI') ))
            then   return false;
            else
            return true;
            end if; 
            end;
            Wrong approach. ALWAYS compare dates as DATE data types. Using string comparison semantics as you are attempting to do here will yield incorrect results. When compared as strings 31-DEC-1999 > 01-JAN-2013 = TRUE:
            SQL> with t as (
              2    select 'Foo' s, date '1999-12-31' d1, date '2013-01-01' d2 from dual)
              3  select
              4      *
              5  from
              6      t
              7  where
              8*      to_char(d1, 'DD-MON-YYYY HH24:MI') > to_char(d2, 'DD-MON-YYYY HH24:MI')
            SQL> /
            
            S   D1                      D2
            --- -------------------------- --------------------------
            Foo 31-DEC-1999 00:00            01-JAN-2013 00:00
            
            
            SQL> with t as (
              2    select 'Foo' s, '1999-12-31' d1, '2013-01-01' d2 from dual)
              3  select
              4      *
              5  from
              6      t
              7  where
              8*      to_date(d1, 'YYYY-MM-DD') > to_date(d2, 'YYYY-MM-DD')
            SQL> /
            
            no rows selected
            What is this? Validation? Condition? What is it supposed check/do?

            What is the data type of <tt>trip.operator_counter</tt>?
            • 3. Re: character conversion error on date comparision in function returning boolea
              Lucy Discover
              ORA-06502: PL/SQL: numeric or value error: character string buffer too small


              DECLARE 
              finish_time date;
              begin 
              
              select max(to_date(sim_trip.finish_time,'DD-MON-YYYY HH24:MI'))  into finish_time from sim_trip
                WHERE ((sim_trip.operator_counter = :P12_operator_counter));
              if (to_date(finish_time,     'DD-MON-YYYY HH24:MI')) > 
                 (to_date(:P12_start_time, 'DD-MON-YYYY HH24:MI'))
                 then  
               return false;
              
              else
              return true;
              end if; 
              end;
              This is a validation on ITEM. To find if the User is not entering a date and time not less than previously enetered Trips Time.
              • 4. Re: character conversion error on date comparision in function returning boolea
                Matthew Morris
                In your 'IF' statement, you were running a TO_DATE on the finish_time variable... which was already a DATE.
                That said, the below 'fix' assumes that the sim_trip.finish_time column is a VARCHAR field. If it is a DATE field, then you can remove the TO_DATE conversion from that as well. The only time you need TO_DATE is when you are converting character data to DATE data.
                DECLARE 
                  v_finish_time   DATE;
                BEGIN 
                 
                  SELECT MAX(TO_DATE(sim_trip.finish_time,'DD-MON-YYYY HH24:MI'))
                  INTO   v_finish_time 
                  FROM   sim_trip
                  WHERE  (sim_trip.operator_counter = :P12_operator_counter);
                 
                  IF v_finish_time > TO_DATE(:P12_start_time, 'DD-MON-YYYY HH24:MI') THEN
                    RETURN FALSE;
                  ELSE 
                    RETURN TRUE;
                  END IF;
                END;
                • 5. Re: character conversion error on date comparision in function returning boolea
                  Lucy Discover
                  Mathew I get ORA-01830: date format picture ends before converting entire input string


                  Error for the Above.
                  • 6. Re: character conversion error on date comparision in function returning boolea
                    Lucy Discover
                    Mathew Actually that helped. I was able to resolve the issue.