December 23, 2009 at 11:29 am
Hey Gang,
I've been trying to figure somthing out for a bit, but I'm having trouble. I have an agent job that executes an SSIS package and sends data to one of our clients. The report is to be based on data created between the run dates of the report. The report MUST run 4 times per month on specific dates: 5th,12th,19th and 26th. My sql script in the SSIS packages currently has these variables which would normally be fine if the report ran on the same day each week, but since there different amounts of days per month, the datediff between the 26th of the previous month and the 5th of the following month can vary.
DECLARE @start_dt DATETIME, @end_dt DATETIME
set @start_dt = (select dateadd(dd,-6,DATEADD(dd,DATEDIFF(dd,0,getdate() ), 0)))
set @end_dt = (select dateadd(dd,0,DATEADD(dd,DATEDIFF(dd,0,getdate() ), 0)))
What is the correct way for me to set the date variables above so I dont miss any data?
TIA,
Code
December 23, 2009 at 11:36 am
Try this one on:
select day(getdate()) + --how far into this month are we
day(getdate()- day(getdate())) --how many days were there last month
-26 --take out 26
This logic should give you the number of days you need to be adding.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 23, 2009 at 12:17 pm
Would something along those lines work?
DECLARE @date datetime
SET @date ='20091226'
SELECT
CASE
WHEN day(@date) % 7 = 5 THEN 'run'
ELSE 'stop'
END
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply