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.
> -----Original Message-----
> From: Christopher G Tantalo [mailto:[EMAIL PROTECTED]]
> Sent: July 25, 2002 2:21 PM
> To: Jean-Claude Girard
> Cc: Kipp James; [EMAIL PROTECTED]
> Subject: Re: Help on querying between dates
>
>
> just a snippet of code that we would do here...
> select 'X'
> from dmg.history h
> 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');
>
> and that works for the actual days specified, end_date and
> begin_date without
> doing a +1 to any of the dates...
> Chris
> --
> -------------------------------
> Just Your Friendly Neighborhood
> _SPIDEY_
>
>
