Re: Why is date arithmetic suddenly broken (7.3.4)

Stan Brown <stanb_at_netcom.com> wrote in message news:8a5gb0$l4o$1_at_slb6.atl.mindspring.net...
> 7.3.4 in HP-UX
>
> Date arithmetic, and comparisons that have worked for years are
> suddenly broken. Oracle seems to know this, nut I am not getting clear
> answers on why.
>
> Given a table mwh_log. containing a column dstamp which is of type
> date.
>
> A query like:
>
> select * from mwh_log where dstamp , (sysdate - 45)
>
> suddenly returns newer rows, not older! In working with oracle I have
> found that if I do a:
>
> select dump(dstamp) from mwh_log, if the results look like:
>
> 199.200..... that I will see this.
>
> If I run the following update on the data:
>
> update mwh_log
> set dstamp =
> dstamp + 0
> where dstamp != dstamp + 0
>
> Then my queries will work as expected. Also the select dump will show
> that the raw data looks like:
>
> 200,100,.........
>
> Oracle at first said that setting NLS_DATE to a specified format in
> init.ora, and shutting down, and restarting oracle would allow new
> records to be inserted correctly. However this is not what I am seeing
>
> The data is inserted by an old vendor supplied program that I only have
> the binary for.
>
> Help! Please.
>
> What's going on here? I would like to
>
> 1. Understand this
>
> 2. Fix it so that it works again.
>
> Any insight, at all would be greatly appreciated.
>
>
> --
> Stan Brown stanb_at_netcom.com

 404-996-6955
> Factory Automation Systems
> Atlanta Ga.
> --
> Look, look, see Windows 95. Buy, lemmings, buy!
> Pay no attention to that cliff ahead... Henry Spencer
> (c) 1998 Stan Brown. Redistribution via the Microsoft Network is
 prohibited.
>

This smells like a Y2k problem. If you don't specify a date mask, anything not in the date you supply, will be derived from the sysdate. The default date mask doesn't have a century, and that should at least have been set to DD-MON-RR instead of DD-MON-YY by you long ago. For now please run a query like
select to_char(dstamp,'DD-MON-YYYY')
from table

and I have the awful feeling you will see what's wrong immediately.

Hth,

Sybrand Bakker, Oracle DBA Received on Wed Mar 08 2000 - 00:00:00 CST