CODEWITHSUNDEEP
sqlsql-serverssisetlssis-2012
hardik rawal
hardik rawal

Reputation: 115

Find first day and last day of previous week in SSIS expression

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

Upvotes: 3

Views: 2117

Answers (2)

Hadi
Hadi

Reputation: 37313

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

Upvotes: 0

MPJ567
MPJ567

Reputation: 553

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.

Upvotes: 3

Related Questions