In SSIS expression how can we store only date from getdate()

Question

I want to store the date from the getdate()-1 in SSIS variables.

Something like when we run the sql query

select CONVERT(varchar(10),getdate()-1,120)

Output - 2017-11-10

I tried the below query in the expression.

@[User::Dates]  =  
           (DT_WSTR,4)YEAR(GETDATE())  
+ RIGHT ( "0" + (DT_WSTR,2)MONTH(GETDATE()),2) 
+ RIGHT ( "0" + (DT_WSTR,2)DAY(GETDATE()),2)

And it gives me the output as

20171111

Can someone tell me what will the expression for the variable. My datatype for variable is string

Solution 1

Solution

You can simply use the following expression to achieve this:

 SUBSTRING( (DT_STR,50, 1256)DATEADD("DAY",-1,GETDATE()) , 1, 10)

It will return only date with the following format 2017-11-11 (yesterday date)

Explanation

According to this Microsoft Document

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

---

Side Note: Other methods may work, but there is a problem when using datepart functions (DATEPART, YEAR, MONTH, DAY) if the day or month is less than 10 you may have another date format (i.e. 2017-1-3 -> yyyy-M-d or 2017-10-1 -> yyyy-MM-d). So you have to use Them with other methods to fix that (i.e. RIGHT("0" + (DT_STR,50,1256)DAY([Column0]),2))

Solution 2

You're almost there.

First, you need to add the dashes (simple string concatenation).

Second, the syntax for getting the previous day is slightly more complex: SSIS doesn't support GETDATE() - 1, so you need DATEADD("DAY", -1, GETDATE()).

Here's what you're looking for:

(DT_WSTR, 4) YEAR(DATEADD("DAY", -1, GETDATE()))
+ "-"
+ RIGHT("0" + (DT_WSTR, 2) MONTH(DATEADD("DAY", -1, GETDATE())), 2) 
+ "-"
+ RIGHT("0" + (DT_WSTR, 2) DAY(DATEADD("DAY", -1, GETDATE())),2)

Comments

[Hadi]

your answer is correct. But there is a simpler way to achieve this. Take a look at my answer. +1

[Chris Mack]

Thanks! (I was aware of the other method, but wanted to stick with the OP as it was so close! ;))