This is a short article I write because it would have saved me some significant time had I been able to google it (no time-travel google (yet) – find articles you will write in the future). So maybe somebody in the future will find this useful 
.
What we were doing is to use a Microsoft Business Intelligence/Reporting Server to report on data in an Oracle production database.
Creating the OLAP cubes in the MS system, everything worked fine until I tried to pull in aggregate data over a date field from the database, giving me the mysterious “Not a legal OleAut date” error message that can be found in the various forums, but none in the context of OLAP cubes.
It’s not brain surgery to determine that somehow either the Oracle schema did have a weird column type – that we could rule out because it was a regular timestamp(6) column which worked nicely in other places of the cube – or the data itself was corrupt.
We started to look at the data in the table with a minimum / maximum query
-
SELECT min(modificationdate), max(modificationdate) FROM fileversion
and were surprised to find the minimum date displayed as “01/24/10″. With month/day/year display this would have been January 24, 2010, wouldn’t it? We expected the data to go well back into 2006. A closer look on this anomaly showed the culprit: One single line in our 10 mio row table had a timstamp date of actually 0010-01-24 instead of 2010-01-24. With the date display cut short to 2 digits, that was hardly noticable. I *am* a fan of ISO 8601!
What the error message was trying us to tell: The date was stored in Oracle allright, even if it were in ancient times, as Oracles datetime and timestamp can represent all dates from January 1, 4712 BC to December 31, 9999 AD. But the Microsoft SQL Server will do only January 1, 1753 to December 31, 9999 AD.
That’s what triggered the error message, once this one row was fixed, the error was gone. If I were Roman Emperor, I’d buy Oracle. Else I’d think about setting up some date constraints in my schema to prevent bogus data from entering – we didn’t find out how this could have happened, so better make sure to have your constraints in place before the bogus data comes along!
In case you wondered why 1753 – I had thought about the Boston Tea Party (wrong, that’s 1773), but actually according to Wikipedia this was the first full year after Britain had adopted the Gregorian calendar. Spain did in 1582, protestant Hannover in 1700, Greece in 1923. I sure do hope Microsoft has not localized too much.
