Problem loading DATE data type in SQL server

[kaps]

I am having problems writing to SQL Server Database(2008) in a DATE column. We have defined DATE data type for a column and we are trying to insert value in the format of 'yyyy-mm-dd'. We get the following error.

Code: Select all

odbc_lkp: Error when checking operator: When binding output interface field "DATE_CALLED" to field "DATE_CALLED": No default type conversion from type "ustring[max=10]" to type "date".

Job Design is :

Code: Select all

Seq File to Join stage to Transformer to ODBC stage.
another ODBC stage coming to Join stage.

When I imported the table definition in DS, it shows as NVarchar(10) for that column. I tried following things.

1. Run with NVarchar(10) in target odbc, lookup odbc and transformer
2. Run with date(10) in target odbc, lookup odbc and transformer
3. Run with varchar(10) in target odbc, lookup odbc and transformer

First, why is it getting converted to NVarchar during import and what should I use ?

Appreciate any help.

[ray.wurlod]

StringToDate() function with extended properties on the input set to Unicode.

[kaps]

I have tried using StringToDate but getting the same error. Actually, the error comes from the Look up ODBC stage.

If I remove the lookup altogether, the job successfully loads the data into table but If I use lookup(join stage) I get this error. I use the lookup to check if the record already exist or not.

Where is the extended properties ? I could not find that out in ODBC stage.

Thanks for your time...

[pandeesh]

Can you try with timestamp datatype with extended properties?
In your columns list, you can see the column called extended .
I fyou want the timestamp with milliseconds, then you can use the extended property.
Then it will be like 2011-02-01 00:00:00.000

Thanks

[kaps]

There is no extended properties in ODBC stage Enterprise stage. We workaround the issue by converting date to char in the lookup sql.