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
+ 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
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: email@example.com | Blogs: SQLBIGeek | SQLServerCentral | BIDN
Twitter: @briankmcdonald | LinkedIn: http://tinyurl.com/BrianKMcDonald