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

SELECT 'ReportName_' + LEFT(REPLACE(CONVERT(VARCHAR(10),GETDATE(),101),'/',''),4) + CONVERT(CHAR(2),RIGHT(YEAR(GETDATE()),2)) AS [FileName]

--ReportName with MMDDYY - Take 2

SELECT

      'ReportName_'

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

      + CONVERT(VARCHAR(2), CASE WHEN LEN(DAY(GETDATE())) = 1 THEN '0' + CONVERT(CHAR(1),DAY(GETDATE())) ELSE CONVERT(CHAR(2),DAY(GETDATE())) END) --day

      + 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