Re: Why is date arithmetic suddenly broken (7.3.4)

Thomas J. Kyte <tkyte_at_us.oracle.com> writes:

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

        Sure, here is an example:

Connected to:
Oracle7 Server Release 7.3.4.0.0 - Production PL/SQL Release 2.3.4.0.0 - Production

SQL> select dump(dstamp) from mwh_log
  2 where dstamp != dstamp + 0 ;

  DUMP(DSTAMP)

  Typ=12 Len=7: 119,200,3,8,13,1,16
  Typ=12 Len=7: 119,200,3,8,13,1,16
  Typ=12 Len=7: 119,200,3,8,13,1,16
  Typ=12 Len=7: 119,200,3,8,13,1,16
  Typ=12 Len=7: 119,200,3,8,13,1,16

  All of these are for record loged today.

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

	OK, Please help me a bit more here if you could. Pleae explaun what you
	mean by "the 7 byte format"? The application is loging the date from an
	internal tag. This tag has data of the folowing format YYYYMMDDHHMISS,
	and I can use a run time debuger to verify that this is correct.

	Is there a way to seewhat the insert statement that they are creating
	looks like? If so how?





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

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