October 21, 2011 at 2:34 am
Hi,
I have a report subscription which needs to run on the 20th day of each month.
However if the 20th day falls on a weekend then the subscription needs to run on the Monday.
This is fine and I can script this, the issue is returning the date for the current month.
select
case
---if the 20th day is on a Saturday add 2 days to return the date on Monday
when datepart(d,getdate()) = 20 and datepart(dw,getdate()) = 7 then getdate()+2
---if the 20th day is on a Sunday add 1 day to return the date on Monday
when datepart(d,getdate()) = 20 and datepart(dw,getdate()) = 1 then getdate()+1
else
---Stuck here need to return the 20th day of the month
getdate()
end as rundate
October 21, 2011 at 3:29 am
I have adapted you script slightly to get the 20th day of the current month. I hope I have understood this correctly.
[font="Courier New"]select
case
---if the 20th day is on a Saturday add 2 days to return the date on Monday
when datepart(d,DayTwenty.[Date]) = 20 and datepart(dw,DayTwenty.[Date]) = 7 then dateadd(d,2,DayTwenty.[Date])
---if the 20th day is on a Sunday add 1 day to return the date on Monday
when datepart(d,DayTwenty.[Date]) = 20 and datepart(dw,DayTwenty.[Date]) = 1 then dateadd(d,1,DayTwenty.[Date])
else
---Stuck here need to return the 20th day of the month
DayTwenty.[Date]
end as rundate
From
(
Select
Cast(Cast(Year(getdate()) As Varchar) + Cast(MONTH(getdate()) As Varchar) + '20' As Date) As [Date]
) As DayTwenty[/font]
Regards
Daniel
October 21, 2011 at 4:39 am
Hi, I think I have not really explained this properly. The script should return the 20th day of the current month. if the 20th day falls on a weeked it will return the day on Monday.
The script will be used to send a report, I have created a data driven subscrition which will run every weekday. If the rundate is the date of the current date then it return a value and the report will run.
I think I have figured it out, to test it have created a report subscrition which will run every weekday. I have changed the day to the 23rd and it seems to work!!
DECLARE @mydate smallDATETIME
SELECT @mydate = (select dbo.wfdatewithouttime( convert(smalldatetime,convert(char(4),datepart(yy,getdate()),103)
+ '-'+convert(char(2),datepart(mm,getdate())) + '-23')))
SELECT * FROM (
select
case
---if the 20th day is on a Saturday add 2 days to return the date on Monday
when datepart(d,@mydate) = 23 and datepart(dw,@mydate) = 7 then @mydate+2
---if the 20th day is on a Sunday add 1 day to return the date on Monday
when datepart(d,@mydate) = 23 and datepart(dw,@mydate) = 1 then @mydate+1
else
@mydate
end as rundate
)
AS D
where rundate=(select dbo.wfdatewithouttime(getdate()) )
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply