Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2002
    Posts
    58

    Post Unanswered: Error converting data type DBTYPE_DBTIMESTAMP to datetime

    I'm getting the error Error converting data type DBTYPE_DBTIMESTAMP to datetime when I try to import a date field from Oracle to SQL Server.

    Code:
    SELECT CAST(cancel_dt as datetime) FROM OPENQUERY(orcldb, 'SELECT cancel_dt FROM tablename WHERE id= 12345')
    The date in Oracle is 19-JUN-99, but it's coming over as 06/19/0999 and it throws the error because the year 999 is invalid in SQL Server. Any ideas on why Oracle or SQL server would convert the year to 0999 instead of 1999?
    This is also happening on a value 24-SEP-07 coming over as 09/24/0207

    Most other date values work, so I'd really like to get in depth into the process that occurs when converting to a SQL Server datetime.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Why not use the conversion function in Oracle to produce a 4 digit year?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2002
    Posts
    58
    I found the problem to be that SQL Server only accepts dates from January 1, 1753 through December 31, 9999 whereas Oracle allows dates January 1, 4712 BC to December 31, 9999 AD.

    So these dates that are coming over with years of 999 or 207 produce an error because they are invaild in SQL Server.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's correct...

    M$ figured that because the calendar was adjusted in that century..(they lost several days) that the date function would produce invalid results...think DATEDIFF, DATEADD, ect

    lunacy

    anyway...your dates are not 0999

    Oracle has the ability to produce the dates you need. The date formatting function in Oracle are far superior to SQL Server...

    Just return that date as a varchar in your select, then convert it....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    So what is the actual 4-digit year representation for 19-JUN-99??? Is it 06/19/0999 or 06/19/1999?
    And what about 24-SEP-07? 09/24/0207 or 09/24/2007?

    If the latter is the answer to both then it's not SQL Server, it's how you bring the data in and how much guessing you want SQL Server to do. You also need to check "two digit year cutoff option".
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Feb 2002
    Posts
    58
    MY problem with the dates is that 19-JUN-99 is supposed to be 1999, but somehow this date was loaded as June 19, 999 instead. So it is coming over correctly. There's just some data cleaning to be done.

    This is what happens when you use someone elses data

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    These 2 calls produce the right result:

    select cast('24-SEP-07' as datetime) --09/24/2007
    select cast('19-JUN-99' as datetime) --06/19/1999
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Check how you set up the linked server to Horacle. See if there is any datetime/currency setting that can affect this.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •