How to get iso week in SSIS datepart function

Question

I am trying to get iso week for the date '2022/10/17' in SSIS datepart column expression. I tried the below expression but did not succeeded.

(DT_WSTR,2)DATEPART("isoww",(DT_DBTIMESTAMP)Date_NewFormat)

I also tried isowk, iso_week, ISO_WEEK but failed. Please help.

Answer 1

I got the answer to this question. to get the iso week from a date in SSIS column expression use the below formula

(DT_WSTR,2)((DATEPART("dy",DATEADD("dd",DATEDIFF("dd",(DT_DBTIMESTAMP)"1/1/1753",(DT_DBTIMESTAMP)SomeDateHere) / 7 * 7 + 3,(DT_DBTIMESTAMP)"01/01/1753")) + 6) / 7 )

in the above formula only enter you date and keep everything as it is. DONOT EDIT 1/1/1753

Answer 2

According to my test, we can use the following expression:

DATEPART("ww", (DT_DBTIMESTAMP) "2022-10-17")

Please refer to the following link and pictures.

DATEPART (SSIS Expression)

Depart Name for Week

Expression

Regards,

Lisa

Answer 3

--Ask if it is monday (dayweek (monday) = 1)

DATEPART("dw",(DT_DBTIMESTAMP)(SUBSTRING(DimTime,1,4) + "-01-01")) == 1 ? 

--If it's monday

DATEADD("week",(DT_I2)(SUBSTRING(DimTime,5,2)) - 1,(DT_DBTIMESTAMP)(SUBSTRING(DimTime,1,4) + "-01-01")) : 

--If it isn't monday

DATEADD("week",(DT_I2)(SUBSTRING(DimTime,5,2)) - 1,DATEADD("day",8 - (DATEPART("dw",(DT_DBTIMESTAMP)(SUBSTRING(DimTime,1,4) + "-01-01"))),(DT_DBTIMESTAMP)(SUBSTRING(DimTime,1,4) + "-01-01")))