SQL server 2008 Date to Char question

JPalatianos · Post by **JPalatianos** » Mon Jan 03, 2011 10:03 am

Hi,
The development team is bringing in a timestamp from SQL server 2008 as
2011-05-05 00:00:00.000 and they need to just grab the date portion as 2011-05-05.

The result needs to be inserted into SQl server 2008 as a date field. I've tried changing their sql to bring the date in as a Character field but then get the following error
CFF_POPULATE_STG_TABLE_NEW_CS_ID_JPTEST..odbcCFFStTable.outStagingData: DSD.BCIOpenW results of SQLColAttributes(eff_dt) gave MetaData mismatch
COLUMN.TYPE Expected = Date Actual =

Any suggestions?
Thanks - - John

JPalatianos · Post by **JPalatianos** » Mon Jan 03, 2011 10:11 am

Some more info....when I bring the date in as a time stamp and then try inserting I receive teh following warning:

Is their a function I can use in the transformer to give me the
2011-05-05 of the eff_dt field currently 2011-05-05 00:00:00.000

Server:NJROS1BBLA0704
Project:CFF
Job No:36
Job name:CFF_POPULATE_STG_TABLE_NEW_CS_ID
Invocation:
Event Number:5496
Event type:Warning
User:PRUDENTIAL\X090842
Timestamp:1/3/2011 11:08:20 AM
Message Id:-1
Message:
CFF_POPULATE_STG_TABLE_NEW_CS_ID..xfmNBSStoCFF.outStagingData: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO cff_dev.cffstg.data_src_cshflow_forcst_stg(data_src_sys_cd, seg_cd, prodt_cd, acty_cd, forcst_id, eff_dt, csh_flow_amt, data_src_as_of_dt) VALUES (?,?,?,?,?,?,?,?)
SQLSTATE=01004, DBMS.CODE=0
[DataStage][SQL Client]Data has been truncated
SQLSTATE=22008, DBMS.CODE=241
[DataStage][SQL Client][ODBC][IBM (DataDirect OEM) ][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.

data_src_sys_cd = "NBSS"
seg_cd = "INDIVSS"
prodt_cd = "SS"
acty_cd = "KNWNSALE"
forcst_id = "200000011737"
eff_dt = 2011-05-05 00:00:00.000
csh_flow_amt = 840.0000
data_src_as_of_dt = 2011-05-05

ray.wurlod · Post by **ray.wurlod** » Mon Jan 03, 2011 2:49 pm

Expected=Date,Actual=

suggests that the data type is not given in the column definition in your job (or in the database).

JPalatianos · Post by **JPalatianos** » Tue Jan 04, 2011 7:20 am

After looking at their job a bit more, It looks like it may be an issue with SQL server 2008 "Date" type. When we import the medadata the column definition comes in as NVarChar. Since we do not have NLS installed we cannot use this and anything else we try gives a conversion error writing to the target table. I have contacted IBM to see if they have an updated ODBC driver or any suggestions for us. I will update the post once I hear anything.
Thanks - - John

cppwiz · Post by **cppwiz** » Fri Jan 07, 2011 1:44 pm

Yes, this is an issue with the DataDirect drivers in the Datastage 8.1 release. You can find out more about this known issue by searching this web site:

http://knowledgebase.datadirect.com/

There is an upgrade to the DataDirect 6.0 drivers available on the IBM FixCentral web site which will resolve this issue.