Re: Query strangeness

Sounds like a problem with NLS settings. Default NLS settings are determined by the character set the database is created with.
This can be changed for the database in init.ora. And can be changed in a session with set commands. Oracle always stores time as part of a date if you are not seeing it is because of settings.

As a quick way to get your query it to work use ROUND( date ). this will make the time 12:00 noon.

Ross Macadam wrote in message <88ja5q$h3a$1_at_infoserv.netkonect.net>...
>We have an oracle 7.3.4 systems that are exhibiting different query
response
>behaviour. After upgrade from 7.2.2 a date field in one of the tables
>started to capture the time of entry along with an entered date (the forms
>4.5 mask capturing this data only allows for date input and the field is
>DATE field type).
>On one of the systems a sql*plus query using the equals clause with a date
>parameter returns no records although there are records in the system
>carrying that specific date. Also less than date clauses omit records from
>the date that is in the clause. On the other system the query works as
>expected even though the date field in question also contains date and time
>(HH:MI) information.
>I suspect that the SQL clause is being applied rigorously in system 1 and
is
>failing because when the date (integer) and Time (decimal) values stored in
>the field are being compared the system is doing a mathematical compare of
>(say) 13.00 with 13.25.
>The questions we are trying to answer are:
>Why did the 7.2.2 > 7.3.4 upgrade cause this field to start capturing a
time
>(HH:MM) component - we did nothing to facilitate this (I think).
>Why does the sql statement behave differently on one system from the way
it
>behaves on the other is there some environmental variable or DB setting
that
>we've overlooked?
>All advice gratefully received ...........
>
>
Received on Mon Feb 21 2000 - 18:47:17 CST