Search code examples
datetimevariablesssisexpressiondateadd

Add a day to a datetime project parameter and store in a variable of type datetime in using SSIS expression

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.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%C2%AE%20Visual%20Studio%C2%AE%202015&ProdVer=14.0.23107.0&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

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

Solution

  • 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