datetime loading differently in sqlserver database

reddy12 · Post by **reddy12** » Tue Jul 01, 2008 6:22 am

gurus,

i have a job that loads a sqlserver table.

i have Oracle Enterprise ---->Transformer----->Odbc Connector.

i am loading a table in Sqlserver using Odbc connector.

during the load process i have oracle date filed which i am reading as a timestamp and loading as a timestamp to sql server ie. as datetime in sqlserver table.

it is loading fine except the date 2007-12-31 14:29:29
it loads as 2008-12-31 14:29:29.000

any ideas why is it loading 2007 as 2008??.

chulett · Post by **chulett** » Tue Jul 01, 2008 6:40 am

No, I don't see how that could be. Double-check that that is really what is happening. What is your derivation for the field? Generated or user-defined sql?

reddy12 · Post by **reddy12** » Tue Jul 01, 2008 7:22 am

Tanks for your response.

uses generated sql statement.
and in transformer derivation i just mapped from timestamp to timestamp.

nothing else..

chulett · Post by **chulett** » Tue Jul 01, 2008 8:00 am

Oracle will generate a TO_CHAR(YourField,'YYYY-MM-DD HH24:MI:SS') in your source SQL for a timestamp, what does SQL Server generate on the isert side? Regardless, I still don't see how it can get the year wrong. For one date.

ps. Thanks, I like tanks. Panzerkampfwagen VI Ausf. B, baby!

reddy12 · Post by **reddy12** » Tue Jul 01, 2008 8:12 am

Thanks!!!!

to test here is what i did

i selected the data from oracle table like this
select * from table_name where load_date = to_date('2007-12-31 14:29:29','yyyy-mm-dd hh24:mi:ss')

and in transformer mapped to odbc and to a peak..

the specific column is loaded as 2008-12-31 14:29:29.000 in sql server and to peak it is written as 2007-12-31 14:29:29

chulett · Post by **chulett** » Tue Jul 01, 2008 8:21 am

Hmmm... a couple of points. That's not generated sql if it literally says 'select *' in it. Without any sort of conversion coming out of Oracle, that will select an internal Oracle numeric value AFAIK, so in my mind all bets are off with regards to what SQL Server will do to it. And you didn't post the ODBC sql, does it 'wrapper' it at all in any kind of function or does it just send it over unmolested?

And a 'peek' stage means a PX job, not Server as you marked your first post.

reddy12 · Post by **reddy12** » Tue Jul 01, 2008 8:27 am

my bad,
when starting to write a post i selected parallel only ..some how wrong selection...
job is parallel..

here is the generated sql on SQLSERVER side..

ODBC_Connector_1,0: [IIS-CONN-ODBC-000105] Connected to Microsoft SQL Server, version 09.00.57867 through driver VMmsss22.so.
[IIS-CONN-ODBC-000099] The driver does not support quoted identifiers in SQL statements
[IIS-CONN-DAAPI-000361] Generated INSERT statement: INSERT INTO table_name(LOAD_DATE) VALUES(ORCHESTRATE.LOAD_DATE)

Regards

chulett · Post by **chulett** » Tue Jul 01, 2008 8:34 am

Hmmm... well, my gun is empty at this point as I'm not that familiar with ins and outs of SQL Server. Hopefully someone else can chime in. That or ping your official support provider and let us know what they have to add.

reddy12 · Post by **reddy12** » Tue Jul 01, 2008 8:37 am

now i am testing with odbc enterprise stage instead of odbc connector..
i should update with the result..

thanks anyway..

reddy12 · Post by **reddy12** » Tue Jul 01, 2008 8:42 am

odbc enterprise stage loads normally..

Thanks.