December 9, 2011 at 4:51 pm
Hi,
I'm pulling data over from a SQL server into my Salesforce org via an Informatica Cloud app. I have a column that had errors (birthday char 8). It wouldn't map into a field we had which was of "date" type (I tried CAST and Convert expressions here but I think there is bad data in the records). I created a new column/field that is a text field to store their birthday.
It comes over just fine in a string character like this: '19820101' . Problem is that I'd like to convert that into a date format 'YYYY-MM-DD' via an expression in Informatica.
Does anyone know an expression to use to accomplish this?
Thanks,
Jonathan
December 9, 2011 at 6:46 pm
you could decompose the string and recompose in a date-like format, the cast and convert both work ok in sql query. dunno about informatica. the date string you provided was 19820101, not sure if it's yyyymmdd or yyyyddmm, you'll have to move the substring around if it's the latter.
select
ISDATE(SUBSTRING(@Bday,1,4) + '-' + SUBSTRING(@Bday,5,2) + '-' + SUBSTRING(@Bday,7,2))
, SUBSTRING(@Bday,1,4) + '-' + SUBSTRING(@Bday,5,2) + '-' + SUBSTRING(@Bday,7,2)
,CAST (SUBSTRING(@Bday,1,4) + '-' + SUBSTRING(@Bday,5,2) + '-' + SUBSTRING(@Bday,7,2) AS DATE)
,CONVERT(DATE , SUBSTRING(@Bday,1,4) + '-' + SUBSTRING(@Bday,5,2) + '-' + SUBSTRING(@Bday,7,2))
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply