June 27, 2008 at 2:16 pm
hi guys i am doing a stored procedure in which sends an email with information from a query from the previous month:
Alter PROCEDURE [dbo].[usp_SENDEMAIL]
AS
DECLARE @AlertMessage VARCHAR(500)
DECLARE @MailSubject VARCHAR(100)
DECLARE @EmailMonth DATETIME
SET @EmailMonth=DATEADD(month, -1, GETDATE())
set nocount on
select @count = count(*)
select count(*)
from tblDistributors where datedeleted = @EmailMonth
BEGIN
SET @MailSubject = 'Previous month Distributor deleted list'
SET @AlertMessage = 'the previous month:' + ***show last month's name****+ 'was' + @count
-- Send out email....
my question is in the part of the previous months in the alert message how can i show the name of the previous month? thank you in advance!
June 27, 2008 at 2:26 pm
I think the only way to get the text-name for the month would be to do a CASE statement on MONTH(DATEADD(m, -1, GETDATE())) to return the correct string for each integer value.
Good Luck!
Chad
June 27, 2008 at 4:43 pm
Hi,
You can even try out in this way
theprevmonth = UCase(MonthName(Month(DateAdd("m", -1, dtsglobalvariables("TodaysDate"))), True))
Thanks -- Vj
June 27, 2008 at 4:50 pm
The DATENAME function might be what you're looking for.
SELECT DATENAME(mm,DATEADD(mm,-1,GETDATE()))
June 30, 2008 at 7:25 am
i tried it but i got this error:
'dtsglobalvariables' is not a recognized built-in function name.
June 30, 2008 at 7:51 am
you are getting a conversion error because DATENAME returns nvarchar, not datetime.
---------------------------------------
elsasoft.org
June 30, 2008 at 9:40 am
got it thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply