none
expression derived coloumn

    Question

  • I have an column in my source table as

    Valyyyymmdd   [Valyyyymmdd] [nvarchar](24) NULL 

    =================

    20130503

    20120403

    00000000

    20110523

    20100715

    I want to get the difference with getdate()

    so i used the below query in my source

    DATEDIFF(DAY, IIF( [Valyyyymmdd] ='00000000', CONVERT(VARCHAR(8), GETDATE(), 112), [Valyyyymmdd]) , getdate()) as SalesStageAging

    but i need to get the Valyyyymmdd and do ssis derived column to get the difference in date resulting in int value.

    I tryed as below

    DATEDIFF("d",[Valyyyymmdd] == "00000000" ? GETDATE() : (DT_DBDATE)(SUBSTRING([Valyyyymmdd],1,4) + "-" + SUBSTRING([Valyyyymmdd],5,2) + "-" + SUBSTRING([Valyyyymmdd],7,2)),GETDATE())

    kindly provide me the expression which has to be written in derived column expression


    ShanmugaRaj


    • Edited by ShanmugaRaj Wednesday, August 20, 2014 11:01 AM datatype
    Wednesday, August 20, 2014 10:10 AM

All replies

  • Shanmugaraj,

    What is your requiement? How will the difference between getdate() and another date look in yyyymmdd format? Could you provide an example? Say the difference between '20121001' and '20140815' in yyyymmdd format.

    Wednesday, August 20, 2014 11:09 AM
  • In table, if the column is having 20140810

    i need the result to be 10 returning in the expression.


    ShanmugaRaj

    Wednesday, August 20, 2014 11:12 AM
  • Please try the following.

    DATEDIFF(DAY, CONVERT(DATE,(CASE WHEN [Valyyyymmdd] = '00000000' THEN CONVERT(NVARCHAR(8),GETDATE(),112) ELSE [Valyyyymmdd] END),112), GETDATE()) AS SalesStageAging


    • Edited by Avijit Swain Wednesday, August 20, 2014 12:15 PM
    Wednesday, August 20, 2014 11:39 AM
  • CONVERT(VARCHAR(8), COALESCE(NULLIF(Column,00000000),GETDATE()), 112)


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

    Wednesday, August 20, 2014 11:48 AM
  • error on updating

    still getting same error


    ShanmugaRaj

    Wednesday, August 20, 2014 12:26 PM
  • Try This:

    DATEDIFF( "D", (DT_DATE) ((SUBSTRING((DT_WSTR, 20) [Valyyyymmdd],1,4) + "-" + SUBSTRING((DT_WSTR, 20) [Valyyyymmdd],5,2) + "-" + SUBSTRING((DT_WSTR, 20) [Valyyyymmdd],7,2))), GETDATE())


    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Wednesday, August 20, 2014 1:56 PM
  • I got the code correct. but on execution, i get the below error



    [Derived Date Diff [32]] Error: An error occurred while evaluating the function.



    [Derived Date Diff [32]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Derived Date Diff" failed because error code 0xC0049067 occurred, and the error row disposition on "Derived Date Diff.Outputs[Derived Column Output].Columns[Derived Column 1]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.


    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Derived Date Diff" (32) failed with error code 0xC0209029 while processing input "Derived Column Input" (33). 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.


    ShanmugaRaj

    Wednesday, August 20, 2014 2:15 PM
  • Can you remove other derived columns and try executing package only for this Datediff column. Check if you get any error msg?

    If this post answers your query, please click "Mark As Answer" or "Vote as Helpful".

    Wednesday, August 20, 2014 2:28 PM
  • Ah you wanted SSIS expression

    ok here you go

    ((DT_WSTR,50)[ColumnName]== "00000000"? GETDATE():(DT_DBDATE)(SUBSTRING((DT_WSTR,10)[ColumnName],1,4) + "-" + SUBSTRING((DT_WSTR,10)[ColumnName],5,2) + "-" + SUBSTRING((DT_WSTR,10)[ColumnName],7,2) ))


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

    Wednesday, August 20, 2014 2:54 PM
  • Note  :

    SourceColumn  [Valyyyymmdd] [nvarchar](24) NULL  

    DestinationColumn   [DestColumn] [int] NULL,

    After Implementing 
    ((DT_WSTR,24)DestColumn == "00000000" ? GETDATE() : (DT_DBDATE)(SUBSTRING((DT_WSTR,10)DestColumn,1,4) + "-" + SUBSTRING((DT_WSTR,10)DestColumn,5,2) + "-" + SUBSTRING((DT_WSTR,10)DestColumn,7,2)))



    [OLE_DST TargetTable [89]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".


    [OLE_DST TargetTable [89]] Error: There was an error with OLE_DST TargetTable.Inputs[OLE DB Destination Input].Columns[DestColumn] on OLE_DST TargetTable.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data.".


    [OLE_DST TargetTable [89]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "OLE_DST TargetTable.Inputs[OLE DB Destination Input]" failed because error code 0xC0209077 occurred, and the error row disposition on "OLE_DST TargetTable.Inputs[OLE DB Destination Input]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.


    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "OLE_DST TargetTable" (89) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (102). 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.


    ShanmugaRaj

    Thursday, August 21, 2014 5:54 AM
  • Shanmugaraj,

    The expression given by Visakh16 will convert your column to DATETIME but you still have to do the datediff after that to arrive at the difference in days as integer.

    It should look something like this (i have changed Visakh's Query to return DATE instead of DATETIME).

    DATEDIFF("dd",((DT_WSTR,24)[YourColumnName] == "00000000" ? (DT_DBDATE) GETDATE() : (DT_DBDATE) (SUBSTRING((DT_WSTR,10)[YourColumnName],1,4) + "-" + SUBSTRING((DT_WSTR,10)[YourColumnName],5,2) + "-" + SUBSTRING((DT_WSTR,10)[YourColumnName],7,2))), (DT_DBDATE) GETDATE())


    • Edited by Avijit Swain Friday, August 22, 2014 7:58 AM Changed column name.
    Friday, August 22, 2014 7:52 AM