Sql-server – Creating a stored procedure and adding date manually to filename

concatsql serverstored-procedurest-sql

I am new to SQL administration and have been tasked with creating some nightly jobs that send an email with certain details contained within a spreadsheet and so far I have the following:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'support',
@recipients = 'test@mail.co.uk',
@subject = 'Post Code Analysis',
@query = N'SET ANSI_WARNINGS OFF;SET NO COUNT ON;select substring(PostOfficeBox,1,4) as ''Postcode''
, COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as ''New Connections''
, COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as ''Domestic Metered''
, COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as ''Commercial Metered Low''
, COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as ''Commerical Metered High''
, COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as ''Domestic Keypad''
, COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as ''Generator''
, COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as ''Commercial Keypad''
from be_crm4_MSCRM.dbo.AccountExtensionBase as a
INNER JOIN be_crm4_MSCRM.dbo.CustomerAddressBase as b ON a.AccountId =     b.ParentId
where New_AccountStage = 7
and AddressTypeCode = 1
and substring(PostOfficeBox,1,2) = ''BT''
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4);',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'pca-test.csv',
@query_result_header =0,
@query_result_separator = ' '

In the @query_attachment_filename I would like to append the date and have tried using SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] and appending this to the file name as follows:

@query_attachment_filename = 'pca-test-' + SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] + '.csv'

Any advice would be greatly appreciated

Best Answer

Declare a variable first - but use format 112 for this. Like this:

DECLARE @query_attachment_filename NVARCHAR(255) = 
    N'pca-test-' + 
    CONVERT(NCHAR(8), GETDATE(), 112) + 
    N'.csv';

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'support',
...
@attach_query_result_as_file = 1,
@query_attachment_filename = @query_attachment_filename,
@query_result_header =0,
@query_result_separator = ' '
Related Question