February 4, 2011 at 12:33 pm
Craig Farrell (2/4/2011)
Also, to the original question:StartDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date) -4, +2))
EndDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date) -3, +1))
ReportDate = CStr(DATEADD("m", DATEDIFF("m", 0, Date)-2, +1))
This query gives the data from 10/01/2010 to 10/30/2010. It suppose to give me from 10/01/2009 to 10/31/2010. I am not getting that why. Can any one explain me in detail how these query pull the data as of now I have to pull data of all the months of 2009 and I am new to SQL Server.
None of this deals with a year. It's all month conversions and nothing over 12 (12 months equaling 1 year). This wasn't meant to do what you expect.
I agree with Craig here as well. The year conversions must be happening someplace else, or you posted the code to the wrong ActiveX task. I'm assuming this package exports data to a CSV or text file, which is the report in question?
You can change my suggested code to alter @StartDate to the year before (though I'd recommend inserting that update after the @EndDate code) which gives you the year range you need. But it will require you learning a little bit of T-SQL if you don't already know it.
February 10, 2011 at 11:02 am
Well,
Actually this Active X script was creating a global variable in SSIS package and use that global variable to execute stored procedure and then load data in temp table and at last create a files on server. currently I executed stored procedure manually with parameter and it work.
I think its a good way to replace Script task with Execute SQL task and declare variable in sql task and execute stored procedure within it.
February 10, 2011 at 11:14 am
WOW!
Declare @StartDate char(10), @EndDate char(10), @ReportDate char(10),
@MonthStart smalldatetime;
Set @MonthStart = DateAdd(m,-3,GetDate());
Set @StartDate = LEFT(Convert(char(24),@MonthStart,120),7) + '-01';
Set @EndDate = LEFT(Convert(char(24),DateAdd(dd,-1,
DateAdd(m,1,Convert(smalldatetime,@StartDate,120))),120),10);
/***********************************************************/
Above code makes my life easy. I wrote the above code in Execute sql task with my stored procedure and :hehe:.........Thank You Brandie Tarvin!!!!!!
February 11, 2011 at 4:08 am
Glad I could help.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply