Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

SSIS expression syntax (w/ variables)

I have a variable
@{User::Path)
I need to append yesterday's date to this variable in this format (yyyymmdd) to the path. For example,
20101107 (If a month or day is single digit, it needs to precede with a zero '0')
0
spirose
Asked:
spirose
  • 3
  • 2
  • 2
  • +1
1 Solution
 
HumpdyCommented:
this was solved earlier.

you can see this link for step by step resolution.
http://www.sqlnerd.com/ssis_dynamic_dates.htm
0
 
spiroseAuthor Commented:
How do I modify this expression to get yesterday's date instead of today's? Thanks in advance!
0
 
Bhavesh ShahLead AnalysistCommented:
This way

SELECT  CONVERT(VARCHAR(10),DateAdd(day,-1,GETDATE()),112)
      
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
spiroseAuthor Commented:
Sorry but this has to be in SSIS expression syntax....
The above only works in SQL Server...
0
 
vdr1620Commented:
You can use the below exp
@[User::Path]+(DT_STR,4,1252)YEAR(Dateadd("D",-1,getdate()))+
Right("0" + (DT_STR,4,1252) Month(Dateadd("d",-1,getdate())),2) +
Right("0" + (DT_STR,4,1252) Day(Dateadd("d",-1,getdate())),2)

Open in new window

0
 
vdr1620Commented:
Actually you can decrease the size..use this modified Exp
@[User::Path]+(DT_STR,4,1252)Year(Dateadd("D",-1,getdate()))+
Right("0" + (DT_STR,2,1252) Month(Dateadd("d",-1,getdate())),2) +
Right("0" + (DT_STR,2,1252) Day(Dateadd("d",-1,getdate())),2)

Open in new window

0
 
Bhavesh ShahLead AnalysistCommented:

I should say sorry.
anyways try this.

Select REPLACE(CAST(DT_DBDATE,DATEADD("day", -1, getdate())),"-","")

there could be some syntax error as I dont have anything to test it
0
 
spiroseAuthor Commented:
Thank you!
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now