July 6, 2015 at 1:29 am
I've created a variable using the expression builder. What I'm trying to do is add a filename which includes yesterday's date. I hit problem when the month changes, the date part is correct, but the month part doesn't change, when the extract ran on the 1st of July the file name should have had 20150630 appended, but instead it had 20150730, the date portion changed but the month portion didn't. How can I change the following code to say that when it's the 1st of the month that it should also subtract 1 from the month portion. Here's my code
"Stock Rec - "+ (DT_STR, 4 , 1252)DATEPART( "year" , GETDATE() ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART( "month" , GETDATE() ) , 2 ) + "" + RIGHT( "00" + (DT_STR, 2 , 1252)DATEPART( "day" , dateadd("day",-1,getdate()) ) , 2 ) + ".csv"
July 6, 2015 at 3:02 am
SELECT convert(varchar,(cast(dateadd(d,-1,GETDATE())AS date)),112)
July 6, 2015 at 3:42 am
That's fine for sql, but it won't work in expression builder, will it ?
July 6, 2015 at 3:54 am
Maybe
=DateAdd(DateInterval.Day, -1, Today)
(wow, makes me hate SSRS!!!)
July 13, 2015 at 7:48 am
Sorry for the delay in responding, been off ill.
this one just errors telling me it's incorrectly formed.
I suppose what I really need is something that checks the year, month and day because if I run on the 1st of January, it will need to subtract 1 from the day, the month and the year also
July 13, 2015 at 11:50 am
"Stock Rec " + (DT_WSTR, 10) (DT_DBDATE) DATEADD( "DD", -1, GETDATE() ) + ".csv"
July 14, 2015 at 12:55 am
That's the one!
One other thing, how can I get rid of the hyphens so it just shows 20150713
July 14, 2015 at 3:49 am
I've sorted it. I originally tried to use 'REPLACE' but couldn't get the syntax right, so I used SUBSTRING instead,
SUBSTRING( @Yesterday, 1, 4 ) + SUBSTRING( @Yesterday, 6, 2 ) +SUBSTRING( @Yesterday, 9, 2 )
Thanks for all your help
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply