7 Replies Latest reply on Sep 28, 2017 8:28 PM by JimatNOAA

    sqldeveloper date comparison

    user9048178

      Hello all.

       

      I'm having a problem with sqldevloper in regards to date comparisons.

       

      Here is basically what I have:

       

      These two variables:

      lv_last_date_worked     date;

      lv_dw_last_working_day  varchar2(20);

       

      Both variables contain the same date value (obviously one in date format, the other in varchar2 format) of '05/25/2017'

       

      I have the following statement:

           if lv_last_date_worked > to_date(lv_dw_last_working_day,'mm/dd/yyyy') --if they don't complete their contract OR

                OR lv_people_group_id  in (26,305,125) then    -- people group id in 26, 305,125 follow the 15th of the month rule

       

      When running my package, I get the following error: v_error_code = ORA-01858: a non-numeric character was found where a numeric was expected

       

      If I change the logic to:

           if lv_last_date_worked >= to_date(lv_dw_last_working_day,'mm/dd/yyyy') --if they don't complete their contract OR

                OR lv_people_group_id  in (26,305,125) then

       

      The immediately above statement WORKS.  The only change was the addition of the '=' sign.

      I don't understand what is happening.

       

      I even went into c:\users\myname\ApppData\Roaming\SQL Developer\system3.0.04.34 and renamed this file and fired up sqldeveloper just in case some settings were off in sqldeveloper.

       

      If I turn the package body into an anonymous block, the date comparison works fine.

       

      Does anybody have any insight?

      Thank you

        • 1. Re: sqldeveloper date comparison

          Wrong forum!

           

          This forum, as the title says, is ONLY for questions/issues regarding Sql Developer.

           

          Please mark the thread ANSWERED and repost in the SQL and PL/SQL forum.

          SQL & PL/SQL

          I'm having a problem with sqldevloper in regards to date comparisons.

          Sql dev doesn't do 'date comparisons. That is done by the Oracle database. And you are NOT comparing dates - you are comparing a date and a string. That causes Oracle to try to implicitly convert the string to a date using the default date format for your session. That format can vary depending on what client software you use and the default settings you are using with it.

           

          You should NEVER rely on 'implicit' transforms but should use TO_DATE to specify the correct format for the string.

          The immediately above statement WORKS.  The only change was the addition of the '=' sign.

          I don't understand what is happening.

          The exception message is telling you 'what is happening'. You have one or more rows with strings that do NOT match the format you are using. That is what happens when you use strings to store date values - someone can enter 'abc' as a date.

           

          You need to find the rows with the garbage data and fix the data.

           

          But that is a SQL question and has NOTHING to do with Sql Developer.

          • 2. Re: sqldeveloper date comparison
            user9048178

            My apologies if my presentation of the question was not clear enough.

            I do believe the problem is with sqldeveloper.

            I've been running the same code for months successfully out of sqldeveloper.

            If I take that same code and turn it into an anonymous block, it works fine (yes, the exact same code).

             

            Nice use of all caps

            • 3. Re: sqldeveloper date comparison

              My apologies if my presentation of the question was not clear enough.

              The question is perfectly clear.

              I do believe the problem is with sqldeveloper.

              Nothing in what you posted indicates that.

              I've been running the same code for months successfully out of sqldeveloper.

              Which supports what I already told you - you have GARBAGE in your data.

              You have one or more rows with strings that do NOT match the format you are using.

              . . .

              You need to find the rows with the garbage data and fix the data.

              I rather doubt if that 'same code' has been using the 'same data' for months.

              If I take that same code and turn it into an anonymous block, it works fine (yes, the exact same code).

              Not sure what your point is. The sample you posted has a compound condition with an OR statement in it. Oracle is free to evaluate that expression in any order it chooses. And it very well could evaluate the 'IN' half of the OR condition first in which case it would not run across that 'bad data' you have in the table.

               

              If I take that same code and turn it into an anonymous block, it works fine (yes, the exact same code).

              Again - that doesn't really mean anything. Also you haven't posted a single thing that supports ANY of your statements.

               

              This is NOT a sql dev problem issue.

               

              If you want help you need to repost in the proper forum.

              • 4. Re: sqldeveloper date comparison
                user9048178

                Perhaps this will shed some light.

                I've modified the program to address your issues.

                I have two variables declared (one newly created to address your criticisms and to demonstrate that the issue is not with conversion to a date format)

                 

                lv_last_date_worked     date;

                lv_wayne_date           date;

                 

                I've modified the section of code you objected to in order to better demonstrate that I believe that I have a sqldeveloper issue (other issues have come up in the past where sqledeveloper starts acting wonky).

                 

                v_loc := '380aaa';
                dbms_output.put_line('v_loc = ' || v_loc);
                lv_wayne_date          := to_date('05/26/2017','MM/dd/yyyy');
                lv_last_date_worked := to_date('05/26/2017','MM/dd/yyyy');
                v_loc := '3806bbb';


                dbms_output.put_line('v_loc = ' || v_loc);
                dbms_output.put_line('lv_last_date_worked = ' || lv_last_date_worked);
                dbms_output.put_line('lv_wayne_date = ' || lv_wayne_date);
                v_loc := '99999'; -- if this value is thrown in the excetpion section, we know that the assignment to date variables was successful and we are not going to do the date comparision logic
                if lv_last_date_worked < lv_wayne_date then
                     v_loc := '3816a';
                     dbms_output.put_line('at 1203');

                else
                    dbms_output.put_line('THE DATE COMPARISON FAILED');
                end if;

                 

                dbms_output statements follow so you can see if the code stepped through the logic at certain points

                v_loc = 380aaa

                v_loc = 3806bbb

                lv_last_date_worked = 05/26/2017

                lv_wayne_date = 05/26/2017

                THE DATE COMPARISON FAILED

                 

                The exception section throws:

                v_loc = 99999  -- indicating that the assignment to the date variables was successful

                v_error_code = ORA-01858: a non-numeric character was found where a numeric was expected                  

                 

                And in case you want to see the exception section in question:

                exception

                when others then

                v_error_code := substr(SQLERRM,1,200);

                insert into aps.apsd_audit_table(description) values ('v_loc = ' || v_loc);

                insert into aps.apsd_audit_table(description) values ('v_error_code = ' || v_error_code);

                • 5. Re: sqldeveloper date comparison
                  EdStevens

                  user9048178 wrote:

                   

                  Perhaps this will shed some light.

                  I've modified the program to address your issues.

                  I have two variables declared (one newly created to address your criticisms and to demonstrate that the issue is not with conversion to a date format)

                   

                  lv_last_date_worked date;

                  lv_wayne_date date;

                   

                  I've modified the section of code you objected to in order to better demonstrate that I believe that I have a sqldeveloper issue (other issues have come up in the past where sqledeveloper starts acting wonky).

                   

                  v_loc := '380aaa';
                  dbms_output.put_line('v_loc = ' || v_loc);
                  lv_wayne_date := to_date('05/26/2017','MM/dd/yyyy');
                  lv_last_date_worked := to_date('05/26/2017','MM/dd/yyyy');

                  Ok, so you set both dates to exactly the same value.  BTW, a DATE always has a time component down to the second.  If, as in this instance, you do not supply it, it will be 00:00:00.

                   

                  v_loc := '3806bbb';


                  dbms_output.put_line('v_loc = ' || v_loc);
                  dbms_output.put_line('lv_last_date_worked = ' || lv_last_date_worked);
                  dbms_output.put_line('lv_wayne_date = ' || lv_wayne_date);
                  v_loc := '99999'; -- if this value is thrown in the excetpion section, we know that the assignment to date variables was successful and we are not going to do the date comparision logic
                  if lv_last_date_worked < lv_wayne_date then

                  And of course that comparison "fails".  You yourself set both dates to exactly the same, so they are equal, not less or greater than.

                  • 6. Re: sqldeveloper date comparison
                    user9048178


                    Thanks for your response, however I believe you missed the point.  The comparison is throwing an Oracle error.  It is not that the dates are equal, the error being thrown is: v_error_code = ORA-01858: a non-numeric character was found where a numeric was expected    

                    • 7. Re: sqldeveloper date comparison
                      JimatNOAA

                      Sorry, I can't reproduce the error:

                       

                      CREATE OR REPLACE PROCEDURE SANDBOX AS

                        regular_date  date ;

                        char_date varchar2(20) ;

                      BEGIN 

                        -- set regular_date prior to char_date

                        regular_date := TO_DATE('05/26/2016','MM/DD/YYYY');

                        -- set char_date to a properly formated date more recent than the real date

                        char_date := '05/26/2017';

                        IF regular_date < to_date(char_date,'MM/DD/YYYY') THEN

                          DBMS_OUTPUT.PUT_LINE('correct - the real date is prior to the char date');

                        END IF;

                         -- set regular_date equal to char_date

                        regular_date := TO_DATE('05/26/2017','MM/DD/YYYY');

                        -- set char_date to a properly formated date more recent than the real date

                        char_date := '05/26/2017';

                        IF regular_date = to_date(char_date,'MM/DD/YYYY') THEN

                          DBMS_OUTPUT.PUT_LINE('correct - the real date not prior to the char date');

                        END IF;

                          -- set regular_date after to char_date

                        regular_date := TO_DATE('05/26/2018','MM/DD/YYYY');

                        -- set char_date to a properly formated date more recent than the real date

                        char_date := '05/26/2017';

                        IF regular_date > to_date(char_date,'MM/DD/YYYY') THEN

                          DBMS_OUTPUT.PUT_LINE('correct - the real date is after the char date');

                        END IF;

                          -- use the <= comparison operator

                        regular_date := TO_DATE('05/26/2017','MM/DD/YYYY');

                        -- set char_date to a properly formated date more recent than the real date

                        char_date := '05/26/2017';

                        IF regular_date <= to_date(char_date,'MM/DD/YYYY') THEN

                          DBMS_OUTPUT.PUT_LINE('correct - the real date is equal to the char date and the <= operator was used.');

                        END IF;

                            -- use the <= comparison operator

                        regular_date := TO_DATE('05/26/2017','MM/DD/YYYY');

                        -- set char_date to a properly formated date more recent than the real date

                        char_date := '05/26/2017';

                        IF regular_date >= to_date(char_date,'MM/DD/YYYY') THEN

                          DBMS_OUTPUT.PUT_LINE('correct - the real date is equal to the char date and the >= operator was used.');

                        END IF;

                       

                       

                       

                      END SANDBOX;

                       

                      onnecting to the database GOL Dev.

                      correct - the real date is prior to the char date

                      correct - the real date not prior to the char date

                      correct - the real date is after the char date

                      correct - the real date is equal to the char date and the <= operator was used.

                      correct - the real date is equal to the char date and the >= operator was used.

                      Process exited.

                      Disconnecting from the database GOL Dev.