SSIS For Loop with DATEADD in assignexpression

Question

I have an SSIS for loop and in the assign expression part of the for loop I need to increment with a DATEADD function but the date part needs to be dependent on my variable:

@DateStart = DATEADD(@TypeIncrement,@DayIncrement,@DateStart)

If i change it ,it will work:

@DateStart = DATEADD(dd,@Increment,@DateStart)

but if I try to do it this way its just giving me an error saying The date part parameter specified for function "DATEADD" is not valid.

I have also tried changing it to be a case statement with different date parts but that wouldn't work either. Any ideas?

Thanks.

----------------EDIT

a lot of ppl are posting sql server answers. but i need it working here:

CASE @TypeIncrement really is your friend here. What's the thing you tried that "wouldn't work"? (And presumably @DayIncrement is a misnomer if it can be about other things than days?) — Jeroen Mostert
– Jeroen Mostert, Commented Apr 13, 2018 at 14:57
yea i need to rename the variable. Basically its not allowing me to pass in a variable for the first parameter. Its breaking when I have the @TypeIncrement there. I've edited my answer abit to help — Budyn
– Budyn, Commented Apr 13, 2018 at 15:00
That's correct, which is why you'd do CASE @TypeIncrement when 'DAY' THEN DATEADD(DAY, ...) WHEN 'MONTH' THEN ... etcetera. The first parameter must be a literal, but you can definitely use CASE. — Jeroen Mostert
– Jeroen Mostert, Commented Apr 13, 2018 at 15:01

John Cappelletti · Accepted Answer · 2018-04-13 15:03:15Z

1

Example

Declare @TypeIncrement varchar(10) = 'MM'
Declare @DayIncrement int = 5
Declare @DateStart date = '2017-03-15'

Select Case @TypeIncrement
       When 'YY' then DateAdd(YY, @DayIncrement, @DateStart) 
       When 'QQ' then DateAdd(QQ, @DayIncrement, @DateStart) 
       When 'MM' then DateAdd(MM, @DayIncrement, @DateStart) 
       When 'WK' then DateAdd(WK, @DayIncrement, @DateStart) 
       When 'DD' then DateAdd(DD, @DayIncrement, @DateStart) 
       End

Returns

2017-08-15

answered Apr 13, 2018 at 15:03

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Budyn Over a year ago

this works in sql but im trying to do it inside SSIS For Loop and its still throwing an error

John Cappelletti Over a year ago

@Budyn Seeing double quotes in your image "dd" should just be DD

Thom A · Accepted Answer · 2018-04-13 15:04:42Z

The first parameter doesn't accept a variable as an input. One alternate option to a CASE might be dynamic SQL:

DECLARE @IncrementType nvarchar(10), @Increment int, @DateStart date;
SET @IncrementType = N'DAY';
SET @Increment = 5;
SET @DateStart = GETDATE();

IF @IncrementType NOT IN (N'YEAR',N'QUARTER',N'MONTH',N'DAYOFYEAR',N'DAY',N'WEEK',N'WEEKDAY',N'HOUR',N'MINUTE',N'SECOND',N'MILLISECOND',N'MICROSECOND',N'NANOSECOND') BEGIN

    DECLARE @Error nvarchar(500) = 'Invalid input for Increment Type(''' + @IncrementType + N'''. Valid options are: YEAR, QUARTER, MONTH, DAYOFYEAR, DAY, WEEK, WEEKDAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND, NANOSECOND.'
    RAISERROR(@Error,11,-1);

END ELSE BEGIN

    DECLARE @SQL nvarchar(MAX);

    SET @SQL = N'SET @dDateStart = DATEADD(' + @IncrementType + N',@dIncrement,@dDateStart)'
    PRINT @SQL; --Your best friend

    EXEC sp_executesql @SQL, N'@dDateStart date OUTPUT, @dIncrement int', @dDateStart = @DateStart OUTPUT, @dIncrement = @Increment;

END

SELECT @DateStart;

This might be over complicating it, but it's an alternate to the CASE anyway.

KeithL · Accepted Answer · 2018-04-13 18:14:12Z

0

implementing inside SSIS:

to get this:

@DateStart = DATEADD(dd,@Increment,@DateStart)

Try this:

@DateStart = @TypeIncrement=="dd" ? DATEADD(dd,@Increment,@DateStart)
             : @TypeIncrement=="mm" ? DATEADD(MM,@Increment,@DateStart)

and so on...

If then else is in the format of:

Test ? true part : false part

answered Apr 13, 2018 at 18:14

KeithL

5,6193 gold badges23 silver badges30 bronze badges

Collectives™ on Stack Overflow

SSIS For Loop with DATEADD in assignexpression

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related