SSIS Expression How to get correct date minus two days

I am trying to get the date for two days ago.  It works with the following, except for when a new month begins.

(DT_WSTR,4)DATEPART("yyyy",GetDate()) +
 RIGHT("0" + (DT_WSTR,2)DATEPART("mm",GetDate()) ,2) +
 RIGHT("0" + (DT_WSTR, 2) DATEPART("DD", DATEADD("day", -2, GETDATE())),2)

Today it yields 20180231, but it should yield 20180130.
Thank you for your help with this.
Who is Participating?
Mark WillsTopic AdvisorCommented:
when I said getdate() - 2, it was in reference to what you are already doing for DATEPART("DD"....

(DT_WSTR,4)DATEPART("yyyy",DATEADD("day", -2, GETDATE())) +
 RIGHT("0" + (DT_WSTR,2)DATEPART("mm",DATEADD("day", -2, GETDATE())) ,2) +
 RIGHT("0" + (DT_WSTR, 2)DATEPART("DD", DATEADD("day", -2, GETDATE())),2)


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

I think your padding is needed when using YEAR() MONTH() DAY() functions

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

But it has been a while :)
And my shorthand speak is more T-SQL oriented
Apologies for any confusion.
Mark WillsTopic AdvisorCommented:
you will need to do the getdate() - 2 wherever you are using getdate().

why not simplify as Cast(getdate() - 2 as date) or use format(getdate() - 2,'yyyyMMdd')
newtoperlpgmAuthor Commented:
I should have clarified I am using it in SSIS as an SSIS expression.  I will try your suggestion.  I hope it works.!  Thank you.
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Mark WillsTopic AdvisorCommented:
Worst case is to make sure you use the first suggestion. Where ever you have getdate() make sure it is getdate() - 2

EDIT: in SSIS parlance that is DATEADD("day",-2,GETDATE())
alpa buddhabhattiCommented:
I think you need to use something like DATEADD("day", -2, GETDATE()).

or ..
you can create 2 seprate variable one for start date and one for number of difference in day..
DATEADD( "dd", @[User::DaysBack] , @[User::CurrentDate])

Or alternatively you could enter:

DATEADD( "dd", @[User::DaysBack] , GETDATE())
newtoperlpgmAuthor Commented:
I will give your suggestion a try.
newtoperlpgmAuthor Commented:
that syntax did not work.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.