I have two dates (startdate,enddate) and I need the difference between the two dates using the datediff function in an SSIS package.
The start and end dates are in the format MMDDYYYY, so first I used derived column to change them into the 'MM/DD/YYYY' format by using the following SUBSTRING function:
SUBSTRING(startdate,1,2)+'/'+SUBSTRING(startdate,3,2)+'/'+SUBSTRING(startdate,5,4)
This function worked great in SSMS, but when I use the same function in SSIS it's throwing errors. I also tried to cast the result adding (DT_DATE) before the function, still no luck, can anyone please point me in the right direction?
Error message:
TITLE: Microsoft Visual Studio
Error at Data Flow Task [Derived Column [34]]: Parsing the expression " SUBSTRING(StartDate,1,2)+'/'+SUBSTRING(StartDate,3,2)+'/'+SUBSTRING(StartDate,5,4)" failed. The single quotation mark at line number "1", character number "32", was not expected.
Error at Data Flow Task [Derived Column [34]]: Cannot parse the expression " SUBSTRING(StartDate,1,2)+'/'+SUBSTRING(StartDate,3,2)+'/'+SUBSTRING(StartDate,5,4)". The expression was not valid, or there is an out-of-memory error.
Error at Data Flow Task [Derived Column [34]]: The expression " SUBSTRING(StartDate,1,2)+'/'+SUBSTRING(StartDate,3,2)+'/'+SUBSTRING(StartDate,5,4)" on "output column "StartDate" (62)" is not valid.
Error at Data Flow Task [Derived Column [34]]: Failed to set property "Expression" on "output column "StartDate" (62)".
