November 9, 2011 at 9:06 am
I have the following expression:
@[Dest_Dir_Deploy] + @[Dest_File] + "_" +
(DT_WSTR,4)DatePart("yyyy", GetDate())
+ "_" +RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2)+ "_"
+RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()), 2) + ".xlsm"
The evaluated value is:
C:\SSIS\TXQuoteActivity\Deployment\FormattedTXQuotes_2011_11_09.xlsm
I need the Date to be 2011_11_08 and I'm having issues with the syntax.
Any help would be greatly appreciated.
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 9, 2011 at 9:13 am
So you want the date in the file name always to be yesterday's date? Best way to do that is to do some date arithmetic and take one day off the current date before you start.
Hope that makes sense
John
November 9, 2011 at 9:33 am
John Mitchell-245523 (11/9/2011)
So you want the date in the file name always to be yesterday's date? Best way to do that is to do some date arithmetic and take one day off the current date before you start.Hope that makes sense
John
Yes, absolutely that makes sense.
I tried GetDate()-1 and the Day part in the above expression and I got a syntax error.
Any hints on how I can do this?
Thanks.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 9, 2011 at 2:29 pm
I get todays date from the expression listed below.
C:\SSIS\TXQuoteActivity\Deployment\FormattedTXQuotes_2011_11_09.xlsm
I tried altering it to GetDate-1 to get the Date to be 2011_11_08 but I got an error.
@[Dest_Dir_Deploy] + @[Dest_File] + "_" +
(DT_WSTR,4)DatePart("yyyy", GetDate())
+ "_" +RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2)+ "_"
+RIGHT("0" + (DT_WSTR,2)DatePart("dd", GetDate()), 2) + ".xlsm"
If I create another variable it will not be evaluated until runtime.
What do I need to do, create a script task to update the Date variable?
Please advise.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 10, 2011 at 1:16 am
Instead of GETDATE()-1, try DATEADD("Day",-1,GETDATE()).
John
November 10, 2011 at 11:46 am
John Mitchell-245523 (11/10/2011)
Instead of GETDATE()-1, try DATEADD("Day",-1,GETDATE()).John
Is this what you meant?
@[Dest_Dir_Deploy] + @[Dest_File] + "_" +
(DT_WSTR,4)DatePart("yyyy", GetDate()) +
"_" +
RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2)+
"_" +
RIGHT("0" + (DT_WSTR,2)DATEADD("day",-1,GETDATE()), 2) + ".xlsm"
I get a truncation error.
Thanks for you help.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 10, 2011 at 2:05 pm
You're getting a truncation error because of this:
(DT_WSTR,2)DATEADD("day",-1,GETDATE())
DATEADD return type is DATETIME. DATETIME is considerably longer than two characters.
Try this instead:
(DT_WSTR, 2) DATEPART("dd", DATEADD("day", -1, GETDATE()))
November 10, 2011 at 2:19 pm
:unsure::crazy:
kramaswamy (11/10/2011)
You're getting a truncation error because of this:
(DT_WSTR,2)DATEADD("day",-1,GETDATE())DATEADD return type is DATETIME. DATETIME is considerably longer than two characters.
Try this instead:
(DT_WSTR, 2) DATEPART("dd", DATEADD("day", -1, GETDATE()))
Unfortunately I get a expression canot be evaluated. :sick:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 10, 2011 at 2:27 pm
What's your full expression?
I tried this, and it worked fine for me:
@[User::TempCreatedFilesFolder] + @[User::OutputFile] + "_" +
(DT_WSTR,4)DatePart("yyyy", GetDate())
+ "_" +RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2)+ "_"
+RIGHT("0" + (DT_WSTR, 2) DATEPART("dd", DATEADD("day", -1, GETDATE())), 2) + ".xlsm"
Results in:
\\[Share]\[Directory]\[File]_2011_11_09.xlsm
November 10, 2011 at 3:10 pm
kramaswamy (11/10/2011)
What's your full expression?I tried this, and it worked fine for me:
@[User::TempCreatedFilesFolder] + @[User::OutputFile] + "_" +
(DT_WSTR,4)DatePart("yyyy", GetDate())
+ "_" +RIGHT("0" + (DT_WSTR,2)DatePart("mm", GetDate()), 2)+ "_"
+RIGHT("0" + (DT_WSTR, 2) DATEPART("dd", DATEADD("day", -1, GETDATE())), 2) + ".xlsm"Results in:
It worked.:cool:
Thanks.
\\[Share]\[Directory]\[File]_2011_11_09.xlsm
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy