November 25, 2013 at 12:05 pm
I have a simple query that provide me with all the previous month data using the FilecreationDate
Declare @StartDate varchar(8)
Declare @EndDate varchar(8)
Set @StartDate = Replace(convert (varchar(8), DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,-1,GETDATE())),112),'/','')
Set @EndDate = Replace(convert(varchar(8), DATEADD(dd, -DAY(DATEADD(m,1,GETDATE())), DATEADD(m,0,GETDATE())),112),'/','')
Select * FROM mytable
where FilecreationDate between @StartDate and @EndDate
I want to use the above query to export the previous month data using the Execute sql task
Variables:
StartDate value Replace(convert (varchar(8), DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,-1,GETDATE())),112),'/','')
EndDate value Replace(convert(varchar(8), DATEADD(dd, -DAY(DATEADD(m,1,GETDATE())), DATEADD(m,0,GETDATE())),112),'/','')
My query in Execute SQL Task
Select * FROM [dbo].[myTable]
where FileCreationDate between ? and ?
when I build my ssis and run I get every record from the table. what am I doing wrong. It looks like my variable parameter is not working. All I want are only records from the 1st of last month to the end of last month.
November 25, 2013 at 12:37 pm
I have solve my issue. I did not need Execute SQL Task. I used sqlcommand
December 11, 2013 at 10:29 am
Just an FYI if you are using the CONVERT with 112 you don't need the REPLACE statement the 112 does not return the results with the slashes(yyyymmdd).
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply