3

I have checked in SQL and get values of first day and last day of previous week, but I want in SSIS expression.

I tried in SQL script

SELECT CAST(DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) AS DATE) --First day of previous week
SELECT CAST(DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) AS DATE) --Last day of previous week
Hadi
  • 36,233
  • 13
  • 65
  • 124
hardik rawal
  • 117
  • 1
  • 2
  • 18

2 Answers2

3

I believe SSIS uses the same expressions as SSRS.

First Day of Previous week would be: 

=DateAdd("d",-DatePart(DateInterval.WeekDay,dateadd("d",-7,Today),0,0)+1,dateadd("d",-7,Today))

Last Day of Previous week would be:

=dateadd("d", 6, DateAdd("d",-DatePart(DateInterval.WeekDay,dateadd("d",-7,Today),0,0)+1,dateadd("d",-7,Today)))

The above is based on a Sunday start and Saturday ending week.

MPJ567
  • 521
  • 5
  • 16
0

Use the following expressions within derived columns:

First day of previous week

DATEADD("wk",DATEDIFF("wk",7,GETDATE()),0)

Last day of previous week

DATEADD("wk",DATEDIFF("wk",7,GETDATE()),6)

References

Hadi
  • 36,233
  • 13
  • 65
  • 124