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.

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.Why not use the conversion function in Oracle to produce a 4 digit year?|||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.|||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...|||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".|||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 :mad:|||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|||Check how you set up the linked server to Horacle. See if there is any datetime/currency setting that can affect this.