none
sp_send_dbmail - how to insert date into subject line of email

    Question

  • Hi There,

    I am wondering if there is a way to include the current date in the format of "Monday 1st January 2010" in to the subject line of an email when using the sp_send_dbmail procedure?

    Many Thanks
    Matt
    Wednesday, February 17, 2010 2:21 PM

Answers

  • Thats great, thanks for the prompt reply - although I am looking at sticking this between text - and the syntax doesnt line @subject = 'Data for '@s,

    Any ideas?

    Thanks
    Matt

    DECLARE @s VARCHAR(max)
    SET @s = 'Data for '+CONVERT(VARCHAR(12),GETDATE(),107)

       

    EXEC msdb.dbo.sp_send_dbmail

    @recipients=@email,

    @body= @msg,

    @body_format = 'HTML',

    @subject = @s

    @profile_name = 'testprofile'

     


    My Name Is Khan should get Oscar!
    Wednesday, February 17, 2010 2:59 PM

All replies

  • Have you tried declareing a variable, format the date, and then include it in the subject line?
    Something like below. Let us know if you need some help formatting the date.


    DECLARE @s VARCHAR(12)
    SET @s = CONVERT(VARCHAR(12),GETDATE(),107)
    EXEC msdb.dbo.sp_send_dbmail 
        @subject = @s

    Abdallah El-Chal, PMP, ITIL, MCTS
    Wednesday, February 17, 2010 2:27 PM
  • Thats great, thanks for the prompt reply - although I am looking at sticking this between text - and the syntax doesnt line @subject = 'Data for '@s,

    Any ideas?

    Thanks
    Matt
    Wednesday, February 17, 2010 2:54 PM
  • Thats great, thanks for the prompt reply - although I am looking at sticking this between text - and the syntax doesnt line @subject = 'Data for '@s,

    Any ideas?

    Thanks
    Matt

    DECLARE @s VARCHAR(max)
    SET @s = 'Data for '+CONVERT(VARCHAR(12),GETDATE(),107)

       

    EXEC msdb.dbo.sp_send_dbmail

    @recipients=@email,

    @body= @msg,

    @body_format = 'HTML',

    @subject = @s

    @profile_name = 'testprofile'

     


    My Name Is Khan should get Oscar!
    Wednesday, February 17, 2010 2:59 PM
  • You have to do something like this

    DECLARE @s VARCHAR(200)
    SET @s = 'Data for ' + CONVERT(VARCHAR(12),GETDATE(),107)
    EXEC msdb.dbo.sp_send_dbmail 
        @subject = @s
    The following will not work

    EXEC msdb.dbo.sp_send_dbmail 
        @subject = 'Data for ' + CONVERT(VARCHAR(12),GETDATE(),107)
    

    Abdallah El-Chal, PMP, ITIL, MCTS
    Wednesday, February 17, 2010 3:01 PM