Solved

SSIS expression syntax (w/ variables)

Posted on 2010-11-08
8
311 Views
Last Modified: 2012-05-10
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
Comment
Question by:spirose
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 10

Expert Comment

by:Humpdy
ID: 34086171
this was solved earlier.

you can see this link for step by step resolution.
http://www.sqlnerd.com/ssis_dynamic_dates.htm
0
 

Author Comment

by:spirose
ID: 34086213
How do I modify this expression to get yesterday's date instead of today's? Thanks in advance!
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34086592
This way

SELECT  CONVERT(VARCHAR(10),DateAdd(day,-1,GETDATE()),112)
      
0
 

Author Comment

by:spirose
ID: 34086610
Sorry but this has to be in SSIS expression syntax....
The above only works in SQL Server...
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 16

Accepted Solution

by:
vdr1620 earned 125 total points
ID: 34086677
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
 
LVL 16

Expert Comment

by:vdr1620
ID: 34086697
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34086715

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
 

Author Closing Comment

by:spirose
ID: 34086754
Thank you!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now