Hunting date errors
Just recently, I had to find out, why a dataload, that had been working smoothlessly in the past, suddenly prodced an error. The data is coming from an ORACLE database, and imported into MS SQL Server with an OPENQUERY statement. The error message read: “Error converting data type DBTYP_DBTIMESTAMP to datetime2”. Since no changes had been done on either side, the culprit was more or less obvious: a new data entry. Searching the internet far and wide, only wielded errors with conversions to datetime. So I was on my own for finding the cause for thisd error.
The first step was trying to find out, which record was the offending culprit. Luckily, there was only one datetime2 column around. My first attempt was to try to find the erroneous record(s) with the TO_CHAR in my Oracle statement:
SELECT * FROM OPENQUERY ( SOME_ORACLE_DB, 'SELECT TO_CHAR(SOME_DBTIMESTAMP), SOME_KEY1, SOME_KEY2 FROM "SOME_SCHEMA"."SOME_TABLE" ') WHERE (TRY_CONVERT(datetime2(0), [TO_CHAR(SOME_DBTIMESTAMP)])) IS NULL
But as you can allready guess, this query returned no rows and so no luck. Well at least it did not produce the same error message.
Next thing to check: lets have a look at the boundaries. I chucked out the WHERE clause, and sorted the resulting dataset on the converted datetime column (ascending as well as descending), which yielded the following boundary results:
“0001-01-01 00:00:00” and “6200-09-08 00:00:00” (as well as a lot of other garbage date values in between). Allright, so I had a look, whether one of these two results was the offending date value. And yes, selecting only the record with the first date threw the error again. Next on the agenda was to make sure, that there was only one record, that gave the error. This panned out as well – whew.
So, what was Happening here? Looking at the definition of datetime2, this value should be okay. Trying to cast or convert the value in the outer SELECT did not help either. Might it have something to do with the timezone setting? The easiest way to check this was to add a couple of hours in the OPENQUERY:
SOME_DBTIMESTAMP + INTERVAL ''25'' HOUR
Still no luck. Getting a bit desperate, I changed the HOUR into YEAR and finally got a result back: “0024-01-01 00:00:00.0000000”. Christ, the date is Before Christ. You see, the ORACLE range for dates starts January 1st 4712 BC. In retrospect, I would have thought that Oracle would have given me back a minus or some other indication that it’s a “negative” date, when converting the date into a character. But it turns out, that you have to ask more directly like so:
TO_CHAR(SOME_DBTIMESTAMP, ''AD'') --or TO_CHAR(SOME_DBTIMESTAMP, ''BC'')
What are the different lessons here?
- There are small differences between the different flavours of SQL Server, that can make your life really hard.
- Have a look at your preconceptions. Just because you think you know something and hear or read it (like that you can convert an Oracle DBTIMESTAMP into a DATETIME2 field in MS SQL without problem) multiple times does not make it true.
- Data Quality is almost always an issue, even if there is no hard error in your dataload. Think of strategies to track and report on these issues. And incorporate those errors into your testing!