DOH! Yep, I surrender. I didn't notice that slight difference, which makes a
*huge* difference.
/me slinks away in a corner.
Chris
Jean-Claude Girard wrote:
> Chris,
>
> your code deals with a varchar2 field (his_call_date) which you translate to
> a date using the same mask used in your between clause. This will work
> because you are effectively truncating the time component of your field
> before comparing it to your boundaries.
>
> > where
> > to_date(substr(h.his_call_date,1,9),'DD-MON-RR')
> > between to_date($begin_date,'DD-MON-RR')
> > and to_date($end_date,'DD-MON-RR');
>
> James case is different because he is dealing with a date field.
>
> Here is a test case I just ran on my Box (oracle 8.1.7)
>
> create table test (val varchar2, tstamp date);
>
> insert into test values ('yesterday',sysdate-1);
> insert into test values ('today',sysdate-1);
> insert into test values ('tomorrow',sysdate-1);
>
> commit;
>
> select val, to_char(tstamp, 'DD/MM/YYYY HH24:MI:SS') from test;
>
> VAL TO_CHAR(TSTAMP,'DD/
> ---------- -------------------
> yesterday 24/07/2002 14:32:06
> today 25/07/2002 14:32:53
> tomorrow 26/07/2002 14:32:56
>
> select val
> from test
> where tstamp between to_date('24/07/02','DD/MM/RR')
> and to_date('26/07/02','DD/MM/RR')
>
> VAL
> ----------
> yesterday
> today
>
> As you can see, the row corresponding to the 26/07/2002 does not come.
