January 2, 2014 at 12:05 am
I have a requirement that I want to make monthly subscription where for each month a mail should be sent to the recipients
taking the last month as a parameter.
For eg:If I create subscription on 2nd of every month and the current month is jan. Mail should be sent for the month of Dec which is previous month. Like that this has to be repeated for every month.
Please help me with the SQL syntax..
January 3, 2014 at 2:27 am
Hi
[Code = "sql"]
DECLARE @dayofmonth int
DECLARE @dateprmonth datetime
/* stores one day before day of the month */
SET @dayofmonth = datepart( dd,dateadd (mm,-1 ,getdate()) ) -1
/* stores the previous month date from current date*/
SET @dateprmonth = dateadd(m,-1,getdate())
SELECT dateAdd(dd,0,DateDiff(dd,0,@dateprmonth-@dayofmonth)) AS [FIRST_DATE_OF_PREVIOUS_MONTH],
dateAdd(dd,0,DateDiff(dd,0,dateAdd(MM,1,(@dateprmonth-@dayofmonth )))) -1 AS [LAST_DATE_OF_PREVIOUS_MONTH]
[/Code]
above script to get first and last day of the previous month, if you run your script for between those two dates and set up a report to report run on 2nd of every month.
done and dusted 🙂
January 3, 2014 at 4:18 am
Thank you very much
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply