4 Replies Latest reply: Apr 15, 2010 11:59 AM by 600830 RSS

    Comparing date to sysdate

    600830
      I'm trying to compare a date from an Oracle database (10g R1) to sysdate but is not working.

      DECLARE
         v_exit_date DATE;   --Let's assume exit_date from the database is today's date '15-APR-10'
      BEGIN
          SELECT 
            exit_date 
          INTO
            v_exit_date
          FROM TABLE_1 WHERE student_id = 3020;
            
         IF v_exit_date = sysdate  THEN
             dbms_output.putline ('Dates are equal');
         ELSE
             dbms_output.putline ('Dates are not equal');
         END;
      END;
      The equal (=) expression is not evaluated. I'm always getting "Dates are not equal" when expecting "Dates are equal". What's wrong with my PL/SQL?

      Thanks

      Edited by: Northstar on Apr 15, 2010 11:32 AM
        • 1. Re: Comparing date to sysdate
          fsitja
          What you probably mean is to compare only the day/month/year part of the date. Don't forget that sysdate contains an hour/minute component.

          Use trunc(sysdate) for that, or maybe even trunc(v_exit_date) as well:
          DECLARE
             v_exit_date DATE;   --Let's assume exit_date from the database is today's date '15-APR-10'
          BEGIN
              SELECT 
                exit_date 
              INTO
                v_exit_date
              FROM TABLE_1 WHERE student_id = 3020;
                
             IF v_exit_date = trunc(sysdate)  THEN -- or perhaps trunc(v_exit_date) = trunc(sysdate), depending on what's stored in that column
                 dbms_output.putline ('Dates are equal');
             ELSE
                 dbms_output.putline ('Dates are not equal');
             END;
          END;
          • 2. Re: Comparing date to sysdate
            600830
            I'm getting this error:

            PLS-00306: wrong number or types of arguments in call to '=' when I used trunc on both side of the expression.
            IF trunc(v_exit_date) = trunc(sysdate)  THEN -- or perhaps trunc(v_exit_date) = trunc(sysdate), depending on what's stored in that column
                   dbms_output.putline ('Dates are equal');
               ELSE
                   dbms_output.putline ('Dates are not equal');
               END;
            exit_date type in the database is DATE.
            • 3. Re: Comparing date to sysdate
              fsitja
              I ran this sample below and it's working on my db:
              Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
              Connected as fsitja
               
              SQL> 
              SQL> set serveroutput on
              SQL> create table table_1 as select sysdate exit_date, 3020 student_id from dual;
               
              Table created
              SQL> DECLARE
                2     v_exit_date DATE;   --Let's assume exit_date from the database is today's date '15-APR-10'
                3  BEGIN
                4      SELECT exit_date
                5        INTO v_exit_date
                6        FROM TABLE_1 WHERE student_id = 3020;
                7     IF TRUNC(v_exit_date) = TRUNC(SYSDATE)
                8       THEN dbms_output.put_line ('Dates are equal');
                9       ELSE dbms_output.put_line ('Dates are not equal');
               10     END IF;
               11  END;
               12  /
               
              Dates are equal
               
              PL/SQL procedure successfully completed
               
              SQL> 
              • 4. Re: Comparing date to sysdate
                600830
                It turns out to be the out parameter in my procedure. Changing the out parameter from VARCHAR2 to DATE fixed the problem.

                Thanks.