Post reply

SQL for Converting Field Type CHAR to Date Format 'YYYY-MM-DD'

  • December 9, 2011 at 4:51 pm

    #391093

    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

    #1419674

    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))

    [font="Courier New"]Looking for a Deadlock Victim Support Group..[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply