Thursday, December 14, 2017

Oracle datetimestamp to SQL Server datetime

Why doesnt this work Arggg... I am a SQL Server worker... why do I need to know Oracle too??? That is what I thought when I came across a situtation where dates were throwing a fit. I was pulling data from Oracle into a SQL database.

I started using the LINKED SERVER, but switched to use OPENQUERY because they work differently. At first I didnt care why they did, but I am now coming back to learn much more about it and explain it. Honestly, I am hoping I will remember I am typing this so that when it comes up again, I will have a place to check! :)

This article is all about the dates, not about other data types (hence the name). I learned an interesting fact: SQL Server allows dates to go back to January 1, 1753, but Oracle allows dates earlier than that.

The problem was the date I was trying to bring into SQL was before that date and therefore threw an error:

Msg 8114, Level 16, State 8, Line 4 
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

OPENQUERY sends the query to the remove server to be executed there, rather than being executed on the server calling the query, which is what a LINKED SERVER does. To make myself remember it better, using the LINKED SERVER gets back all raw data from Oracle, then processes the query more, which means the dates we want are included and therefore throws an error. On the other hand, using OPENQUERY makes the Oracle database process the query, which means the dates are not included in the returned recordset. It also means that with OPENQUERY we can use the Oracle functions, which is very handy at times.

Here is an example of my original query where Column3 is a datetimestamp datatype:

select Column1
, Column2
, Column3
from SERVER..DATABASE.TABLEA;

This throws the error shown above.

 

My first attempt at solving the issue was to use the Oracle function TO_DATE and give a different value for the dates I didn’t want. Here is what I ended up with:

select *
from openquery(SERVER,'select Column1
, Column2
, case when Column3 < TO_DATE(''17530101'',''YYYYMMDD'') then null 
else Column3
end from server..DATABSE.TABLEA');

This worked well for my first encounter - and made me more than thrilled!! You can substitute other values in by using TO_DATE('99991231',YYYYMMDD') or some other value. I liked the values going null because to me I wasn’t pulling the date and I didn’t have to remember which date I set it to later on. (yes, I could just do a max, but that takes work :P).

The next time I came across an issue was when the Oracle date field was used in the where clause of the query. In this case, I needed just a date part. Here is the end result:

select *
from openquery(SERVER,'select Column1
, Column2
, case when Column3 < TO_DATE(''17530101'',''YYYYMMDD'') then null 
else Column3
end from server..DATABSE.TABLEA where EXTRACT(YEAR from Column3) >= ''2013''');

 

Again, I was excited about learning more about Oracle. I will have to admit, I chuckled at how the extract function uses more of a "plain english" type of syntax. I was nice that I found the solution a LOT faster this time, I just searched for articles that talked about working with Oracle functions, since I went through the growing pains earlier while learning about OPENQUERY. I had known it was there, but since I had never used it, I didn’t give it much thought . :)

* Note: The double quotes are because you need to pass a quote around the text values, and the double quote is what is used as an escape. Therefore, the query is sent with only 1 quote.

Here is an article I found that helped point me in the right direction early on was from Jon Paul Cook - hopefully this helps you too!
http://sqlblog.com/blogs/john_paul_cook/archive/2009/07/31/linked-server-vs-openquery-for-handling-data-type-conversions.aspx

Archives

Powered by mod LCA