5 Replies Latest reply: Nov 23, 2012 4:36 PM by sb92075 RSS

    date and equality predicate

    905989
      Hi gurus,

      I would like to use a date column in a query by equating it to cerain day.

      For example, I created a table with date column (eff_date as date) and added the following dates
      create table testme (eff_date date not null)
      
       insert into testme values (sysdate);
      
      insert into testme values (trunc(sysdate));
      
      insert into testme values (sysdate-1);
      
       select * from testme;
      
      EFF_DATE
      -------------------
      23/11/2012 21:31:16
      23/11/2012 00:00:00
      22/11/2012 21:32:35
      
      select * from testme where eff_date = '23/11/2012';
      
      EFF_DATE
      -------------------
      23/11/2012 00:00:00
      
      select * from testme where eff_date > '23/11/2012';
      
      EFF_DATE
      -------------------
      23/11/2012 21:31:16
      
       select * from testme where eff_date >= '23/11/2012'
      
      EFF_DATE
      -------------------
      23/11/2012 21:31:16
      23/11/2012 00:00:00
      So in the code above the equality predicate eff_date = '23/11/2012' only displays the exact value at start of day? If I wanted all entries for today I will have to do
      select * from testme where eff_date between '23/11/2012' and '24/11/2012' order by 1;
      
      EFF_DATE
      -------------------
      23/11/2012 00:00:00
      23/11/2012 21:31:16
      Is my understanding correct?

      Thanks
        • 1. Re: date and equality predicate
          sb92075
          902986 wrote:
          Hi gurus,

          I would like to use a date column in a query by equating it to cerain day.

          For example, I created a table with date column (eff_date as date) and added the following dates
          create table testme (eff_date date not null)
          
          insert into testme values (sysdate);
          
          insert into testme values (trunc(sysdate));
          
          insert into testme values (sysdate-1);
          
          select * from testme;
          
          EFF_DATE
          -------------------
          23/11/2012 21:31:16
          23/11/2012 00:00:00
          22/11/2012 21:32:35
          
          select * from testme where eff_date = '23/11/2012';
          
          EFF_DATE
          -------------------
          23/11/2012 00:00:00
          
          select * from testme where eff_date > '23/11/2012';
          
          EFF_DATE
          -------------------
          23/11/2012 21:31:16
          
          select * from testme where eff_date >= '23/11/2012'
          
          EFF_DATE
          -------------------
          23/11/2012 21:31:16
          23/11/2012 00:00:00
          So in the code above the equality predicate eff_date = '23/11/2012' only displays the exact value at start of day? If I wanted all entries for today I will have to do
          The purpose of TO_CHAR is to display a DATE datatype in some desired format.
          The purpose of TO_DATE is to convert string datatype to DATE datatype.
          With Oracle characters between single quote marks are STRINGS!
          'This is a string, 2009-12-31, not a date'
          When a DATE datatype is desired, then use TO_DATE() function including format.

          '10-11-12'
          Which is correct DATE below for string above?
          Oct. 11 2012
          Nov. 10 2012
          Nov. 12 2010
          Dec. 11 2010
          Oct. 12 2011
          Dec. 10 2011
          I'll give you 6 guesses, since the first 5 will be incorrect.


          select * from testme where to_char(trunc(eff_date), 'YYYY-MM-DD') = '2012-11-23'

          Handle:     902986
          Status Level:     Newbie
          Registered:     Dec 17, 2011
          Total Posts:     52
          Total Questions:     13 (9 unresolved)

          why so MANY unanswered questions?
          • 2. Re: date and equality predicate
            Frank Kulash
            902986 wrote:
            Hi gurus,

            I would like to use a date column in a query by equating it to cerain day.

            For example, I created a table with date column (eff_date as date) and added the following dates
            create table testme (eff_date date not null)
            
            insert into testme values (sysdate);
            
            insert into testme values (trunc(sysdate));
            
            insert into testme values (sysdate-1);
            
            select * from testme;
            
            EFF_DATE
            -------------------
            23/11/2012 21:31:16
            23/11/2012 00:00:00
            22/11/2012 21:32:35
            
            select * from testme where eff_date = '23/11/2012';
            '23/11/2012' isn't a DATE; it's a VARCHAR2. Don't try to compare a DATE with a VARCHAR2; the results will depend on several factors, some related to the database, some realted to your session.
            Use TO_DATE to convert a VARCHAR2 (such as '23/11/2012') to a DATE, or else use DATE literals, such as
            DATE '2012-11-23'
            EFF_DATE
            -------------------
            23/11/2012 00:00:00
            
            select * from testme where eff_date > '23/11/2012';
            
            EFF_DATE
            -------------------
            23/11/2012 21:31:16
            
            select * from testme where eff_date >= '23/11/2012'
            
            EFF_DATE
            -------------------
            23/11/2012 21:31:16
            23/11/2012 00:00:00
            So in the code above the equality predicate eff_date = '23/11/2012' only displays the exact value at start of day?
            Basically, yes; if you don't specify hours, minutes or seconds, they default to 00:00:00. But remember, you're comparing a DATE to a VARCHAR2, so anything could happen.
            If I wanted all entries for today I will have to do
            select * from testme where eff_date between '23/11/2012' and '24/11/2012' order by 1;
            
            EFF_DATE
            -------------------
            23/11/2012 00:00:00
            23/11/2012 21:31:16
            Is my understanding correct?
            The most efficient way is
            WHERE   eff_date  >= TO_DATE ('23/11/2012', 'DD/MM/YYYY')
            AND     eff_date  <  TO_DATE ('24/11/2012', 'DD/MM/YYYY')
            The following works, too:
            WHERE     TRUNC (eff_date) = TO_DATE ('23/11/2012', 'DD/MM/YYYY')
            but it's not as efficient, because it can't use an index on eff_date, and it has to call a function for every row being tested.

            Be careful using BETWEEN, because BETWEEN includes both end-points. So
            WHERE   eff_date  BETWEEN TO_DATE ('23/11/2012', 'DD/MM/YYYY')
                        AND       TO_DATE ('24/11/2012', 'DD/MM/YYYY')
            will pick all the rows where eff_date is any time on Nov. 23, but it will also pick rows where eff_date is right on the stroke of 12 for the following day.
            • 3. Re: date and equality predicate
              905989
              Understood

              So I will try
              select * from testme where eff_date = TO_DATE('23/11/2012 00:00:00','DD/MM/YYYY  hh24:mi:ss');
              
              EFF_DATE
              -------------------
              23/11/2012 00:00:00
              
              -- and for every entry for today
              
              select * from testme where eff_date BETWEEN TO_DATE('23/11/2012 00:00:00','DD/MM/YYYY  hh24:mi:ss')
              and TO_DATE('23/11/2012 23:59:59','DD/MM/YYYY  hh24:mi:ss');
              
              EFF_DATE
              -------------------
              23/11/2012 21:31:16
              23/11/2012 00:00:00
              I hope I got it correct now.

              Thanks again
              • 4. Re: date and equality predicate
                sb92075
                902986 wrote:

                I hope I got it correct now.
                ALTERNATIVELY

                select * from testme where to_char(eff_date, 'YYYY-MM-DD') = '2012-11-23';

                but the downside doing as above it disables any index that may exist on EFF_DATE.
                • 5. Re: date and equality predicate
                  rp0428
                  >
                  I hope I got it correct now.
                  >
                  Yep! That's it.

                  But don't forget you would typically have an index on that DATE column if you use the column in queries to select data.