SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Appending Date to End of Report Name in Reporting Services Subscription

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: bmcdonald@pragmaticworks.com | Blogs: SQLBIGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald | LinkedIn: http://tinyurl.com/BrianKMcDonald


Posted by Anonymous on 11 November 2010

Pingback from  Dew Drop – November 11, 2010 | Alvin Ashcraft's Morning Dew

Posted by khurram_iftik15 on 2 October 2013

Where to add this code???

Can you please advise how you are doing it?

Leave a Comment

Please register or log in to leave a comment.