How load varchar into date type in SQL Server using Informatica?

0
votes

Error while moving data from one table (nvarchar) to another table (date) within SQL Server 2014 using IICS (Informatica Cloud). I tried using expression 

To_date_out = To_date(Incep_Date, 'yyyy-mm-dd')

but the same error still shows up:

TT_11019 There is an error in the port [Incep_Date]: The default value for the port is set to: ERROR(Transformation error: IUpdateStrategyNode12)enter image description here

1
informaticainformatica-powercenter
In an expression, you do not do the assignment. Just put to_date(incep_date, 'yyyy-mm-dd'). No need for the to_date_out = part. - bitbangs
additionally, be sure that your incoming nvarchar dates are formatted exactly like the format string you are specifying (yyyy-mm-dd). if perhaps, those come from a text field somewhere, a user may not have properly entered the date. - bitbangs
Thanks bitbangs.Although I’ve shown here both output port & exprsn, I’ve put only the latter as you mentioned i.e.not with the assignment. The format appears YYYY-MM-DD in source table with data type nvarchar & I’ve tried all different formats, trimmed etc - same error. As expected, I verified it writing to target file & it writes in YYYY-MM-DD successfully,but when target is another table - it fails. Goal :Nvarchar -> Date within SQL server using Informatica. - Hem
Looked up the error message TT_11019. It appears that you have a string port linked to a date and you need to edit your session-level datetime formatting or change the formatting your doing in your expression to match whatever your session is set to. - bitbangs
Yep-Same post I guess I looked up yesterday too. Tried changing format as per session and am here to solve error that comes up while doing so. - Hem

1 Answers

0
votes

I found the answer. It seems Informatica implicitly converts the varchar in YYYY-MM-DD to YYYY/MM/DD, despite the session and the source data in a different(former) format.So in the expression, we need to convert to the latter format & it works! TO_DATE( Incep_Date, ‘YYYY-MM-DD’). Thanks @bitbangs for your time & help.