This discussion is archived
5 Replies Latest reply: Nov 23, 2012 2:36 PM by sb92075 RSS

date and equality predicate

905989 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points