asked on

SSIS Expression How to get correct date minus two days

Hello,
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.

Mark Wills

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')

newtoperlpgm

ASKER

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.

Mark Wills

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 buddhabhatti

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())

newtoperlpgm

ASKER

I will give your suggestion a try.

newtoperlpgm

ASKER

that syntax did not work.

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial