Post reply

Error converting data type DBTYPE_DBTIMESTAMP to datetime.

  • September 27, 2006 at 5:25 am

    #192390

    Hi,

    I am running an SQL statement that connect ORACLE database to retrieve data. I tried to run my SQL in sql analyzer (SQL 2000) and I received an error message "Server: Msg 8114, Level 16, State 8, Line 1

    Error converting data type DBTYPE_DBTIMESTAMP to datetime." Is there any work around on how to solve this problem. I tried different options but I failed. I haven't tried the OPENQUERY.. any ideas and help is highly appreciated.

    SQL STATEMENT:

    select * from tfr

    where  exists (select * from NIMS..NI.NI_TFR_MAIN a where substring(a.tm_tfr_no,0,12) = tfrefno and

              a.tm_tfr_received_date_by_lt >='2000-06-08' and '2000-06-08' < a.tm_tfr_received_date_by_lt)

    SQLUser

     

  • September 28, 2006 at 9:07 am

    #663036

    Check the date entries in Oracle for invalid SQL Server dates.  I think Oralce uses a 10 bit and SQL Server uses 8 bit, so you could have dates that would be valid in Oracle that are not valid in SQL Server.

    You should use OPENQUERY:

    SELECT * FROM OPENQUERY(LinkedServerName, 'SELECT * FROM [SomeTable] WHERE [SomeDateField] is NULL OR [SomeDateField] > ''1/1/1753''');

    SELECT * FROM OPENQUERY(LinkedServerName, 'SELECT * FROM [SomeTable] WHERE [SomeDateField] is NULL OR [SomeDateField] < ''12/31/2053''');

    Or you could cast the date as a varchar:

    SELECT * FROM OPENQUERY(LinkedServerName, 'SELECT CAST([SomeDateField] AS varchar(26)) FROM [SomeTable])

    Whatever you do, you need to remove the invalid date values before they get to the SQL Server.  You are likely trying to import a date outside of the 1/1/1753 to 12/31/2053 range and it cannot be converted to datetime.

    Joshua Perry
    http://www.greenarrow.net

  • April 11, 2007 at 10:39 am

    #699847

    Hy 

    I am running an SQL statement that connect other intance of SQL database to retrieve data. I tried to run my SQL in sql analyzer (SQL 2000) and I received an error message "Server: Msg 8114, Level 16, State 8, Line 1

    Error converting data type DBTYPE_DBTIMESTAMP to datetime."

    Is there any work around on how to solve this problem.

    Query:

    Select fecha_proceso, fecha_registro

    From cta.cuenta.dbo.afectacion

    where fecha_proceso        = '20070313'    

    and   fecha_registro     < '03/13/2007' +' 23:59:59.000'

    Thanks

  • April 11, 2007 at 10:52 am

    #699855

    can you post the DDL for dbo.afectacion table ?

    Timestamp is *not* a datetime in MSSQL


    * Noel

  • April 11, 2007 at 11:09 am

    #699865

    Noel the table structure is:

    fecha_proceso        -->

    fecha_registro

  • April 11, 2007 at 11:11 am

    #699866

    Noel the table structure is:

    fecha_proceso        --> DT_FECHA_PROCESO  (CHAR(8))

    fecha_registro        --> DATETIME

  • March 17, 2008 at 5:10 pm

    #790627

    Thanks Joshua Perry..

    It is very helpful.

  • June 30, 2008 at 12:43 pm

    #836317

    Thanks! It worked

  • February 26, 2009 at 11:22 pm

    #950999

    Thanks Joshua, am new to oracle and the CAST worked.

  • January 20, 2010 at 12:26 pm

    #1106257

    It was really helpful. thank you

  • May 11, 2017 at 1:43 pm

    #1942224

    Hello,

    An Oracle perspective on this one.
    In Older versions of Oracle storing dates before 01-JAN-1901 does not conform to Oracle's own rules, it stores December as month zero not month 12 internally. I have a test case with Oracle 10g.

    When OLE DB tries to decode the date 31-DEC-1900, its not a valid date, Oracle's own client tools must know how to handle a month zero.
    Try making all dates (on your test system first!) >= 01-JAN-1901 on the Oracle side, retry your query from the Linked Server.

    Regards,
    Frank

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply