SSIS derived column expression for date difference

Question

I have an column in my source table as [Valyyyymmdd] [nvarchar](24) NULL:

Valyyyymmdd 
=================
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.

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

shree.pat18 shree.pat18 · Accepted Answer · 2014-08-20T10:45:46

Try this:

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

Assuming [Valyyyymmdd] is a string in the format "yyyyMMdd", we split it into the year, month and date parts, insert hyphens between parts to format it as "yyyy-MM-dd" and then cast it as a date i.e. datatype DT_DBDATE. Finally, we use the conditional operator to check if value equals "00000000", and compare either current date, or the date parsed from input with current date to return the result.

SSIS derived column expression for date difference

1 Answers