CODEWITHSUNDEEP
sql-servert-sqlvariablesssissql-server-2012
user3203331
user3203331

Reputation: 437

SSIS Expression for date of previous Friday

How can I get the date of a previous Friday using an SSIS expression? Using below expression:

RIGHT("0" + (DT_WSTR, 2)  MONTH(dateadd("d", -1, getdate())), 2) 
+ RIGHT("0" + (DT_WSTR, 2) DAY(dateadd("d", -1, getdate())), 2)
+ RIGHT("1"+(DT_WSTR, 4)  YEAR(dateadd("d", -1, getdate())),2)+ ".csv"

The output of above expression is 021014.csv

Upvotes: 2

Views: 3856

Answers (2)

DataAnswer
DataAnswer

Reputation: 46

this can also be used :

      @[User::Destination] + "_"+ (DT_WSTR,20)
     (
     DATEPART("dw",GETDATE())==1? RIGHT("0"+(DT_WSTR,2)Month (DATEADD ("D", -  1        GETDATE()) ),  2)
     +
     RIGHT ("0"+(DT_WSTR,2)DAY(DATEADD("D",-1,GETDATE())),2)
     +
     RIGHT("1"+(DT_WSTR,4)YEAR(DATEADD("D",-1,GETDATE())),2) 
     :
     DATEPART("dw",GETDATE())==2? RIGHT("0"+(DT_WSTR,2)    Month(DATEADD("D",-1,GETDATE())),2)   
    +
     RIGHT ("0"+(DT_WSTR,2)DAY(DATEADD("D",-2,GETDATE())),2)
    +
    RIGHT("1"+(DT_WSTR,4)YEAR(DATEADD("D",-1,GETDATE())),2) 
    :
    DATEPART("dw",GETDATE())==3? RIGHT("0"+(DT_WSTR,2)    >Month(DATEADD("D",-1,GETDATE())),2)
    +
    RIGHT ("0"+(DT_WSTR,2)DAY(DATEADD("D",-3,GETDATE())),2)
    +
    RIGHT("1"+(DT_WSTR,4)YEAR(DATEADD("D",-1,GETDATE())),2) 
    :
    DATEPART("dw",GETDATE())==4? RIGHT("0"+(DT_WSTR,2)    >Month(DATEADD("D",-1,GETDATE())),2)
     +
     RIGHT ("0"+(DT_WSTR,2)DAY(DATEADD("D",-4,GETDATE())),2)
     +
     RIGHT("1"+(DT_WSTR,4)YEAR(DATEADD("D",-1,GETDATE())),2) 
     :
     DATEPART("dw",GETDATE())==5? RIGHT("0"+(DT_WSTR,2)    >Month(DATEADD("D",-1,GETDATE())),2)
    +
     RIGHT ("0"+(DT_WSTR,2)DAY(DATEADD("D",-5,GETDATE())),2)
     +
     RIGHT("1"+(DT_WSTR,4)YEAR(DATEADD("D",-1,GETDATE())),2) 
     :
     DATEPART("dw",GETDATE())==6? RIGHT("0"+(DT_WSTR,2)Month (DATEADD("D",-1,GETDATE())),2)
      +
     RIGHT ("0"+(DT_WSTR,2)DAY(DATEADD("D",-6,GETDATE())),2)
      +
      RIGHT("1"+(DT_WSTR,4)YEAR(DATEADD("D",-1,GETDATE())),2)
      :
      DATEPART("dw",GETDATE())==7? RIGHT("0"+(DT_WSTR,2)    >Month( DATEADD(" D",-1,   GETDATE()     )),2)
        +
       RIGHT ("0"+(DT_WSTR,2)DAY(DATEADD("D",-7,GETDATE())),2)
        + 
        RIGHT("1"+(DT_WSTR,4)YEAR(DATEADD("D",-1,GETDATE())),2)
        : "" )
         +".csv"

Upvotes: 3

user275683
user275683

Reputation:

This is using T-SQL, you will still need to convert for SSIS. This is more of hint that an answer. But could not fit it into a comment.

DECLARE @CurrentDate DATE = '02/15/2014' --Saturday
--Returns Date for Friday that was last week. When current date is Saturday will return Friday that was 1 week ago.
SELECT DATEADD(dd,-DATEPART(weekday,@CurrentDate)-1,@CurrentDate)   --Output: 2014-02-07
--Returns Date for most recent Friday.
SELECT  DATEADD(dd,-(DATEPART(weekday,@CurrentDate)%7+1),@CurrentDate)  --Output: 2014-02-14

SET @CurrentDate = '02/11/2014' --Tuesday
SELECT DATEADD(dd,-DATEPART(weekday,@CurrentDate)-1,@CurrentDate)   --Output: 2014-02-07
SELECT  DATEADD(dd,-(DATEPART(weekday,@CurrentDate)%7+1),@CurrentDate)  --Output: 2014-02-07

You need to figure out what is your requirement for getting Friday, is it Friday of last complete week, or just last Friday whereas it is current week or previous.

Upvotes: 0

Related Questions