Error converting data type DBTYPE_DBTIMESTAMP to datetime.

• SqlUser-369680

  Old Hand

  Points: 384

  More actions

  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

   

• Joshua M Perry

  SSCrazy

  Points: 2676

  More actions

  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

• Edison Velasco

  SSC Veteran

  Points: 255

  More actions

  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

• noeld

  SSC Guru

  Points: 96590

  More actions

  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

• Edison Velasco

  SSC Veteran

  Points: 255

  More actions

  April 11, 2007 at 11:09 am

  #699865

  Noel the table structure is:

  fecha_proceso        -->

  fecha_registro

• Edison Velasco

  SSC Veteran

  Points: 255

  More actions

  April 11, 2007 at 11:11 am

  #699866

  Noel the table structure is:

  fecha_proceso        --> DT_FECHA_PROCESO  (CHAR(8))

  fecha_registro        --> DATETIME

• umalachu

  Old Hand

  Points: 341

  More actions

  March 17, 2008 at 5:10 pm

  #790627

  Thanks Joshua Perry..

  It is very helpful.

• Ash-799757

  SSC Journeyman

  Points: 81

  More actions

  June 30, 2008 at 12:43 pm

  #836317

  Thanks! It worked

• barkha.singh

  SSC-Addicted

  Points: 417

  More actions

  February 26, 2009 at 11:22 pm

  #950999

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

• vanaja.mudundi-1101245

  SSC Enthusiast

  Points: 114

  More actions

  January 20, 2010 at 12:26 pm

  #1106257

  It was really helpful. thank you

• fgordonie

  Grasshopper

  Points: 17

  More actions

  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