none
Derived column transformation from varchar(8) YYYYMMDD to DATETIME column

    Question

  • It seems I have tried everything, but all I need to do is change a column from varchar(8) to DATETIME. Some lines are blank, zero length strings, and some have dates such as 20140829. All the rows go to an exception table or it just bombs altogether. The only thing I can get to run is NULL(DT_DATE), but obviously, that won't work in the end.

    I tried LTRIM(RTRIM(MyDateColumn) == "" ? (DT_DATE)"1900-01-01" : (DT_DATE)(SUBSTRING(MyDateColumn,1,4) + "-" + SUBSTRING(MyDateColumn,5,2) + "-" + SUBSTRING(MyDateColumn,7,2))

    I got truncation errors on that.

    Sometimes I got an error: An error occurred while attempting to perform a type cast.

    I tried DT_DATE, DT_DBDATE and DB_DBTIMESTAMP

    I even tried putting GETDATE() in there or a literal date such as "2014-08-29" like (DT_DBDATE)"2014-08-29"

    This wouldn't even compile: MyDatCeolumn == "" ? NULL(DT_DBDATE) : (DT_STR,10,1252)((DT_DBDATE)MyDateColumn)

    I tried many variations on these. I either got truncation errors or the transformation would turn red and stop, or the expression wouldn't work (turns red when you leave the row).

    Review:

    Source MyDateColumn VARCHAR(8) NULL; Target MyDateColumn DATETIME; Source values in format of "" (blank) or "20140829". The blanks should end up null, or maybe a literal date like Jan 1, 1900, and the actual dates should be transformed to DATETIME.

    Should not be that hard. Thanks in advance for help.

    Friday, August 29, 2014 11:13 PM

Answers

All replies

  • can you try this?

    (LEN( TRIM([MyDateColumn]))==0?(DT_DATE)"01/01/1900":(DT_DATE)(SUBSTRING( TRIM([MyDateColumn]),5,2) + "/" + SUBSTRING(  TRIM([MyDateColumn]),7,2) + "/" + SUBSTRING(  TRIM([MyDateColumn]),1,4)))


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, August 30, 2014 5:50 AM
  • I appreciate this, but I have tried and tried, but cannot get it to compile. This huge tool tip pops up with an error when I hover over the red text, but I can't read it fast enough. 
    Tuesday, September 02, 2014 3:20 PM
  • I did get it to compile, but I am still getting a truncation error:

    [Derived Column [26377]] Error: The "component "Derived Column" (26377)" failed because truncation occurred, and the truncation row disposition on "input column "MyDateColumn" (61347)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Derived Column" (26377) failed with error code 0xC020902A while processing input "Derived Column Input" (26378). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

    In review, the input column is varchar(8), and the column that it is going to is datetime. Could the fact that the interim length of the string, being (10) have something to do with it? ("20140902") --> ("09/02/2014") -->  ("2014-09-02 00:00:00.000")

    Tuesday, September 02, 2014 3:42 PM
  • Hi duanewilson,

    Please create a Derived Column transformation and a new column, then use the following expression for the expression field:

    (DT_DATE)(SUBSTRING([MyDateColumn],1,4) + "-" + SUBSTRING([MyDateColumn],5,2) + "-" + SUBSTRING([MyDateColumn],7,2))

    If you have any feedback on our support, please click here.


    Elvis Long
    TechNet Community Support

    Thursday, September 04, 2014 2:47 AM
    Moderator