Data Mismatch using ODBC connector stage

Sreenivasulu · Post by **Sreenivasulu** » Mon Aug 17, 2009 8:52 am

Hi All

1.I pass a column containing varchar as 31/12/2010
2.Apply a StringToDate function
3.In the Sqlserver database its stored as 31/12/2011

Any idea how to fix this ?

Regards
Sreeni

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon Aug 17, 2009 8:59 am

What if you write in a sequential file or to peek ?

chulett · Post by **chulett** » Mon Aug 17, 2009 9:15 am

Post your syntax.

dsuser_cai · Post by **dsuser_cai** » Tue Aug 18, 2009 10:02 am

I use a to_char function to convert the date to string if i use a ODBC stage.

Sreenivasulu · Post by **Sreenivasulu** » Tue Aug 18, 2009 10:08 am

HI Craig,

Pls find the code
======
StringToDate(Left(DSLink5.MaturityDate,10) ,"%dd/%mm/%yyyy")
======

karthick: I am not selecting a date value but inserting a date value

Regards
Sreeni

dsuser_cai · Post by **dsuser_cai** » Tue Aug 18, 2009 10:18 am

use single quote:

StringToDate(Left(DSLink5.MaturityDate,10) ,'%dd/%mm/%yyyy')

ArndW · Post by **ArndW** » Tue Aug 18, 2009 10:20 am

No difference between single and double quotes in these stages. But what value is written to a peek stage or sequential file?

Sreenivasulu · Post by **Sreenivasulu** » Tue Aug 18, 2009 10:30 am

Hi ArndW,

Peek stage and sequential stage gives 31/12/2010

Regards
Sreeni

ArndW · Post by **ArndW** » Tue Aug 18, 2009 10:47 am

Do other dates get loaded correctly or is there always 1 year difference?

Sreenivasulu · Post by **Sreenivasulu** » Tue Aug 18, 2009 10:57 am

All other dates load correcty only this one has a year difference

Regards
Sreeni

Sreenivasulu · Post by **Sreenivasulu** » Tue Aug 18, 2009 11:10 am

Hi Karthick,

The suggestion provided by you did not work. It still gives the same bug

Regards
Sreeni

ArndW · Post by **ArndW** » Tue Aug 18, 2009 11:23 am

And if you manually use SQL to do the same insert statement is the error the same?

Sreenivasulu · Post by **Sreenivasulu** » Tue Aug 18, 2009 12:02 pm

That is my last hope. But i do not want to do this way since then we need to change all the jobs to user-defined sql

Regards
Sreeni

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Tue Aug 18, 2009 2:37 pm

I have used SQL Server in Windows PX Platform. Never had such issues with date.

Try creating a dummy table with 2 columns - varchar and datetime.

Pass same value (after typecast) to both and check the result.

Also run an sql insert via SQL Server front-end rather than DataStage to verify the outcome.

ray.wurlod · Post by **ray.wurlod** » Tue Aug 18, 2009 5:09 pm

Is there a trigger or similar database object that might modify the date?