Re: SQL and to_date problem

A copy of this was sent to Holger Kramer <hkramer_at_gmx.net> (if that email address didn't require changing) On Tue, 08 Sep 1998 14:53:28 +0200, you wrote:

>Hi Thomas!
>
>Thank you for your answering.
>
>Thomas Kyte wrote:
>
>> To query this and use the index, you may use something like:
>> select count(*)
>> from ia_artikel
>> where ausgabedatum between to_date('28.08.1999','DD.MM.YYYY')
>> and to_date('28.08.1999 23:59:59','DD.MM.YYYY HH24:MI:SS')
>>
>> That will get all of the values on a given date without performing a function on
>> the database column.
>
>No! The statement seems to be correct but ... no rows. :-((
>
>Do you know something else which could solve my problem. Maybe a language problem
>german/english in Oracle?
>
>Bye,
>Holger
>

No, not since we are using explicit formats on the to_dates -- no issues there.

Try this script in sqlplus -- it should return 3 rows at the end:

create table ia_artikel_TEST ( ausgabedatum date );

insert into ia_artikel_TEST values
( to_date( '28.08.1999 11:01:01', 'dd.mm.yyyy hh24:mi:ss' ) );

insert into ia_artikel_TEST values
( to_date( '28.08.1999 00:00:00', 'dd.mm.yyyy hh24:mi:ss' ) );

insert into ia_artikel_TEST values
( to_date( '28.08.1999 23:59:59', 'dd.mm.yyyy hh24:mi:ss' ) );

select count(*)
  from ia_artikel_TEST
 where ausgabedatum between to_date('28.08.1999','DD.MM.YYYY')

                    and to_date('28.08.1999 23:59:59','DD.MM.YYYY HH24:MI:SS');
-----------------------------------------------------------------------------

It tests the boundary values on the 28'th of August (midnight going into the 28'th -- 00:00:00 and midnight leaving the 28'th 23:59:59 as well as a random interior point).

If it does not return a count of 3 then something is wrong. If it does return a count of 3 then you don't have any data in your real table for the date you were querying on...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.