Printed 2015/11/28 03:06PM

Appending Date to End of Report Name in Reporting Services Subscription

By Brian K. McDonald, 2010/11/09

Recently, a friend of mine asked me how he could have the date appended to the end of a file name when creating a standard subscription in Reporting Services. At first the requirement was straight forward as it only needed date and it would need to be saved to a file share somewhere. At this point, he was naming the file in a standard subscription, then putting @TimeStamp at the end of it. This however returns a format of “mmddyyyy hhmmss” format. So, I told him to switch the subscription type to Data Driven and then write a query similar to the one in script 1 below.


Script 1: First Requirement of Date Appended

--ReportName with MMDDYYYY

SELECT 'ReportName_' + REPLACE(CONVERT(VARCHAR(10),GETDATE(),101),'/','') AS [FileName]


After he delivered the report, the requirement changed. Go figure right? So, now they only wanted to have it in mmddyy format (2 digit year). So, I thought about it for a second and then modified my script.


Script 2: New Requirement Needs 2 Digit Year

--ReportName with MMDDYY - Take 1



--ReportName with MMDDYY - Take 2



      + CONVERT(VARCHAR(2), MONTH(GETDATE()))         --month


      + CONVERT(CHAR(2), RIGHT(YEAR(GETDATE()),2))    --year

      AS [FileName]


I personally think take 1 was better than take 2, but I would love to hear how you think he could do it?


I hope that you have enjoyed this quick post. If you did, please take just a moment to rate it below! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs.



Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: | Blogs: SQLBIGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald | LinkedIn:

Copyright © 2002-2015 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.