1

I want a SSIS expression which will give me the sunday date of last week.

Corresponding sql server query looks like :

select convert(date,DATEADD(wk, DATEDIFF(wk, 6, convert(date,@report_dt)), -1))

I want the same result in SSIS expression.

Thanks

diptarana mitra
  • 100
  • 4
  • 9

2 Answers2

0

Assuming your date is held in a variable called report_dt, this works for me:

(DT_DATE)LEFT((DT_WSTR, 30)DATEADD("Day", -1 * (DATEPART("Weekday", @[User::report_dt]) + 7), @[User::report_dt]), 10)

However not sure if this is region safe - but it works in the UK.

Dave Sexton
  • 10,768
  • 3
  • 42
  • 56
  • Hi, I was able to see RIGHT function in the String functions folder in SSIS, but it was not able to recognize LEFT function. Am I missing something? – diptarana mitra Oct 30 '15 at 05:01
  • I am using the latest version version so maybe it's a new feature, how about using SUBSTRING instead? – Dave Sexton Oct 30 '15 at 20:55
0

This will give you the Sunday of last week using the variable report_dt:

(DT_DBDATE)DATEPART("dw",@[User::report_dt]) == (DT_DBDATE)1 ? @[User::report_dt] : DATEADD("day",-(DATEPART("dw",@[User::report_dt]) - 1),@[User::report_dt])

It's casted as DT_DBTIME so only the date is returned.

Mark He
  • 735
  • 7
  • 14