Yesterday's date in SSIS package setting in variable through expression
Asked Answered
S

5

6

I am setting a variable in SSIS package and I'm using this expression:

DATEPART("yyyy", GETDATE())*10000 
        + DATEPART("month", GETDATE())*100  
        + DATEPART("day",GETDATE())

The expression will give me a variable value like 'yyyymmdd'. My problem is that I want yesterday's date.

For example on 11/1/2014 it should be 20141031

Stringboard answered 8/11, 2014 at 18:44 Comment(0)
C
11

You can use DATEADD function your expression would be :

DATEPART("yyyy", DATEADD( "day",-1, GETDATE()))*10000 + DATEPART("month",  DATEADD( "day",-1, GETDATE())) * 100 + DATEPART("day", DATEADD( "day",-1, GETDATE()))
Chimney answered 10/11, 2014 at 10:46 Comment(0)
S
6

This will give yesterday's date

(DT_WSTR, 4) YEAR(DATEADD("day",-1,GETDATE())) +RIGHT("0" + (DT_WSTR, 2) DATEPART("MM", DATEADD("day", -1, GETDATE())),2) +RIGHT("0" + (DT_WSTR, 2) DATEPART("DD", DATEADD("day", -1, GETDATE())),2)

Shipyard answered 6/7, 2016 at 15:54 Comment(0)
W
3

less code...

CONVERT(varchar(8), DATEADD(dd,-1,GETDATE()),112)
Waistcloth answered 23/5, 2016 at 14:37 Comment(2)
This doesn't work in an expression - at least not in version 10 (Visual Studio 2008)Ere
This is TSQL, not an SSIS expression, therefore not an answer to the question.Titania
W
1

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

Above will also give you the date one day before with Year() and Month() instead of Datepart(), which makes it slightly shorter.

Wifely answered 6/7, 2017 at 1:22 Comment(0)
B
0

The following example gives the date yesterday with hours and minutes: 2015-09-06-14-40

(DT_WSTR, 4) Year(dateadd("day",-1,getdate())) +  "-" 
+ ( month(dateadd("day",-1,getdate())) < 10 ? "0" + (DT_WSTR, 4) month(dateadd("day",-1,getdate())):(DT_WSTR, 4) month(dateadd("day",-1,getdate()))) 
+ "-" +( day(dateadd("day",-1,getdate())) <10 ? "0" + (DT_WSTR, 4) day(dateadd("day",-1,getdate())):(DT_WSTR, 4) day(dateadd("day",-1,getdate()))) 
+ "-" + right("0"+(DT_WSTR,4)datepart("hh",getdate()),2) 
+ "-" + right("0"+(DT_WSTR,4)datepart("mi",getdate()),2)
Betrothed answered 7/9, 2015 at 12:43 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.