Re: Problem pulling data from oracle

Bobby Singh wrote:
> Hi,
>
> I am trying to write a procedure in SQL server which is pulling data
> from oracle db using OLE and updating tables. I am running into an date
> time issue. Here's the select statement
>
> SELECT DISTINCT R.AGREEMENT_NUM AS PAID, R1.RELATIONSHIP_SUBTYPE AS
> PSubType
> FROM ORCLOLE..PRF_USER.RELATIONSHIP R,
> ORCLOLE..PRF_USER.RELATIONSHIP_SUBTYPE R1
> WHERE ((UPPER(R.AGREEMENT_NUM) LIKE 'DAR%') OR
> (UPPER(R.AGREEMENT_NUM) LIKE 'CAN%'))
> AND UPPER(R.AGREEMENT_NUM) NOT LIKE 'DAR99%' AND
> UPPER(R.AGREEMENT_NUM) NOT LIKE 'DAR88%'
> AND UPPER(R.AGREEMENT_NUM) NOT LIKE 'CAN%.' AND* R.END_DATE >=
> DATEADD(day, -120, GETDATE())***
>
> The getdate portion of this is giving an error
>
> Error converting data type DBTYPE_DBTIMESTAMP to datetime
>
> Basically in oracle its just sysdate-120 but because SQL server doesn't
> recognize sysdate, it errors out there.
> Another thing, it was working fine with 8i. As soon as we upgraded to
> 9i, this started happening. Does anyone know a compatible equivalent of
> getdate that both systems might recognize?
>
> Bobby
>

According to the following Microsoft kb article, a DBTYPE_DBTIMESTAMP is down to billionths of a second.

   http://support.microsoft.com/default.aspx?scid=kb;en-us;299905

According to Note 149118.1 (http://metalink.oracle.com):

<quote>
Oracle9i introduces/externalizes many features for date functionality as part of Oracle's commitment to conform to ANSI SQL specifications.

Data is normalized to a database time zone when stored in the Oracle database and adjusted to the session time zone when the data is selected by users.
</quote>

Sounds like that's why it started happening in 9i. Received on Thu Jan 09 2003 - 18:35:41 CST