Re: Why is date arithmetic suddenly broken (7.3.4)

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/03/08
Message-ID: <8a5ldk$hco$1@nnrp1.deja.com>#1/1

In article <8a5gb0$l4o$1_at_slb6.atl.mindspring.net>, stanb_at_netcom.com (Stan Brown) wrote:
> 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.
>

I believe your vendor has a y2k issue and does not properly encode a date in the 7 byte format which we are expecting... They should be using to_char() and to_date() but it sounds like they are not (you can tell by looking at the sql in v$sqlarea -- see if their "insert into mwh_log ( column, column, column ) values ( :b1, :b2, :b3 )" has a to_date on the bind variable for dstamp or not. If it does not they are using the 7 byte internal format which we 'trust' is correct.

> 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,.........

it should look more like this:

ops$tkyte_at_8i> select to_char(x,'dd-mon-yyyy') , dump(X) dump from t;

TO_CHAR(X,' DUMP

----------- -----------------------------------

01-jan-1900 Typ=12 Len=7: 119,100,1,1,1,1,1 01-jan-2000 Typ=12 Len=7: 120,100,1,1,1,1,1

can you cut and paste the exact dump (the whole thing?)

>
> 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
>

It sounds like your vendor is using the 7 byte internal format for dates and is doing it incorrectly. When you do not use TO_DATE() and TO_CHAR() to deal with dates and use the 7 byte format -- we do not do any validation on the information you send to us. We take the 7 bytes you bind and put them into the database. Your vendors application is doing this incorrectly (that is part of the reason why the NLS_DATE format did not work -- support thought this might be a y2k issue that that the date "03-mar-00" was getting inserted as 1900, not 2000 hence it would be older then 45 days. since that is not the case.... it did not work).

If they are using the 7 byte format -- they are doing it wrong and there is no easy fix other then to fix the code itself. By doing the update, you are 'fixing' it as when we operate on the date, we put it back correctly.

> 2. Fix it so that it works again.
>

One idea that might work is to create a trigger like:

create or replace trigger my_trigger
before insert on mwh_log for each row
declare

l_date date;
begin

l_date := :new.dstamp+0;
:new.dstamp := l_date;
end;
/

it in effect does your update upon insert. That should (but I haven't tested it as I

> 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.
>

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Wed Mar 08 2000 - 00:00:00 CST