convert ssis expression datetime to int

user_6590812 0
Viewed 1807

I want to calculate last friday's date in ssis. Below code is doing it.

DATEADD("dd", -1 - (DATEPART("dw", getdate()) % 7), getdate())

But it gives results for datetime datatype and I want results for int data type in ssis.

How to convert?

DATEADD("dd", -1 - (DATEPART("dw", getdate()) % 7), getdate())
etl ssis datetime expression sql-server
1 Answers

According to this Microsoft article:

When a string is cast to a DT_DATE, or vice versa, the locale of the transformation is used. However, the date is in the ISO format of YYYY-MM-DD, regardless of whether the locale preference uses the ISO format.

In order to convert value to integer yyyyMMdd,

  1. First you have to convert the value to a string
  2. Get the first 10 digit
  3. Remove the - separator
  4. Convert to integer.

You can use the following expression:

(DT_I4)REPLACE(SUBSTRING((DT_WSTR,50)DATEADD("dd", -1 - (DATEPART("dw", getdate()) % 7), getdate()),1,10),"-","")
user_70312300
Related