I am trying to add a day to a project parameter of type DATETIME and store the results in a variable in SSIS which of type DATETIME.
I am using this below expression in variable
dateadd(day,1,(DT_DATE)(DT_DBDATE) @[$Project::Start_Date])
and getting this below error
TITLE: Expression Builder
Expression cannot be evaluated.
------------------------------ ADDITIONAL INFORMATION:
The expression contains unrecognized token "day". If "day" is a variable, it should be expressed as "@day". The specified token is not valid. If the token is intended to be a variable name, it should be prefixed with the @ symbol.
Attempt to parse the expression "dateadd(day,1,(DT_DATE)(DT_DBDATE) @[$Project::Start_Date])" failed and returned error code 0xC00470A4. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.
(Microsoft.DataTransformationServices.Controls)
------------------------------ BUTTONS:
OK
Can anyone help me to resolve the above problem.
CodePudding user response：
TL;DR;
The argument to the first parameter for dateadd is a string, not a constant/enumeration so it should be
dateadd("day",1,(DT_DATE)(DT_DBDATE) @[$Project::Start_Date])
The long way around
I assume the desire is to get the next day's date with the supplied expression
dateadd(day,1,(DT_DATE)(DT_DBDATE) @[$Project::Start_Date])
When I run into issues with expressions, I break them down into the most atomic statement and then compose from there.
I'm using a SSIS scoped variable instead of a project parameter but the logic will hold true.
I have an SSIS variable, Start_Date of data type DateTime with an initial value of 2022-06-01 09:22 AM (convert that to your current locale's preference for date presenation)
I created a new variable, Start_DateOnly and used the following expression
(DT_DATE)(DT_DBDATE) @[User::Start_Date]
Great, that shows 2022-06-01 (no time component in the Variables window although if you evaluate in the Expression editor, it will show midnight). And the explainer - we convert to the DT_DBDATE datatype to drop the time component but DT_DBDATE is incompatible with the displayed DateTime data type so we explicitly convert to DT_DATE.
Cool beans, now all we need to do is confirm the dateadd function works as expected with our new variable
dateadd(day, 1, @[User::Start_DateOnly])
What the heck?
Expression cannot be evaluated.
The expression contains unrecognized token "day". If "day" is a variable, it should be expressed as "@day". The specified token is not valid. If the token is intended to be a variable name, it should be prefixed with the @ symbol.
Oh... yeah, while this language is similar to TSQL, the datepart parameter is a string, not an enum/constant so the syntax should be
dateadd("day", 1, @[User::Start_DateOnly])
Yup, that evaluates to 2022-06-02 12:00 AM