Wednesday, July 3, 2013

DBA: SSRS City Stories: @ExecutionTime date format for SSRS Subscriptions

Had an weird issue today. Sudarshan logs a ticket complaining about the date format in the subject title of the  emails sent from his SQL Server Reporting Services subscriptions. It's in the US format (MM/DD/YYYY) instead of the AU format (DD/MM/YYYY).

Googled around, and found that the date of the subscriptions was set by the variable @ExecutionTime. Further goolging mentioned that the date format cannot be set. But this cannot be, as the dates of the subscriptions prior to the 30th of March were fine!

Searched other sites, and found that the date format of @ExecutionTime is based on the value of the locale field in the subscriptions table inside the ReportServer database. Manually changing this value from "en-US" to "en-AU" solves the issue and gives the correct date format. However, any new subscriptions that Sudarshan creates is still in the "en-US" format! So where does SSRS pull this value from???

I guessed that it pulls the value from the Language parameter of the report. Sudarshan tests this out, but it was not the case, so back to Google I go.. and that was where I found my answer once again. SSRS pulls this value from the user's browser language setting! WTF?!

Case closed. :D