SSIS expression for date comparison?

Question

My expression is like following:

DATEDIFF("dd",(DT_DATE)(SUBSTRING(@[User::strExcelFileName],15,2) + "-" + SUBSTRING(@[User::strExcelFileName],18,2) + "-" + SUBSTRING(@[User::strExcelFileName],21,4)),GETDATE()) > @[User::intDaysCount]

My file : TempConfigPr_06172013.xlsx

The problem is it's saying error while converting from "DT_WSTR" to data type "DT_DATE"...Could any one please help, where I'm going wrong and how to solve this?

Note: I checked,this expression is working fine.

DATEDIFF("dd",(DT_DATE)("11-18-2010"),GETDATE()) > @[User::intDaysCount]

To cast a string to a date, use the CONVERT() function rather than a direct cast. [Here is an example](http://stackoverflow.com/a/1510131/425809). — Richard, Jun 17 '13 at 14:20
@Richard I believe the problem the OP is encountering is they would like to do this in the SSIS expression language and not in TSQL. — billinkc, Jun 17 '13 at 14:37
Ah, I'm thinking SSMS. My mistake. (I had a feeling there was something I was overlooking...) — Richard, Jun 17 '13 at 14:40

score 2 · Accepted Answer · answered Jun 17 '13 at 15:04

Editing expressions in SSIS can be painful. I find when they aren't working as expected, it's best to break them into multiple variables and have them feed off each other.

By doing so, I quickly determined that your root problem was this expression was not generating a valid date value. It generated 61-20-3.xl

SUBSTRING(@[User::strExcelFileName],15,2) + "-" 
+ SUBSTRING(@[User::strExcelFileName],18,2) + "-" 
+ SUBSTRING(@[User::strExcelFileName],21,4)

I created a variable strExcelFileDate of type string and I used the expression to create a string that is the 8 characters following the underscore.

SUBSTRING(@[User::strExcelFileName],  FINDSTRING(@[User::strExcelFileName], "_", 1)+1, 8)

That string value, 06172013, cannot be directly cast to a date, which is a pity. I needed to slice and dice that string into something that can be cast to a date data type. A new Variable named dtExcelFileDate with a type of DateTime was created. I used an expression on that to transform the string into yyyy-mm-dd and then cast that entire thing to a DT_DATE data type.

(DT_DATE) (RIGHT(@[User::strExcelFileDate], 4) + "-" 
+ SUBSTRING(@[User::strExcelFileDate], 1,2) 
+ "-" + RIGHT(SUBSTRING(@[User::strExcelFileDate],1,4), 2))

SSIS expression for date comparison?

1 Answers1