SSRS - Formatting DateTime

I am using SQL Server 2005 Reporting Services and need to format dates. I want the dates in short date format (example: 05/31/2008). I can do this by using the format property of a field and using an expression such as the following:
=Format(Fields!LAST_BILLING_DATE.Value,"MM/dd/yyyy")
 or
=FormatDateTime(Fields!EXPIRATION_DATE.Value,2)

This renders properly in my report and I have no issues. However, in both of these cases when I export to Excel, I get the error " File Error. Some number formats may have been lost.". What happens when the Excel file opens is that the dates are now in numeric format (example: 39753). I believe this is the number of seconds since a certain point in time. If I change the cell format, I can convert the number into a date just fine, however, when the end -users run the reports and export to Excel, I don't want them to have errors and have that extra step each time.

If anyone has any ideas, they would be greatly appreciated. Please let me know if you need any additional information.

Thanks!
LVL 7
Joel MillerDevOps EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

shorakCommented:
A stab in the dark... do you get any dates that come out in date format or do all the dates come out as numeric? I'm wodering if excel reads the date in a different region format.. ie your exporting the date in MM/dd/yyyy but excel is expecting dd/MM/yyyy and hence falls over when it gets an invalid month. As I have said.. I am taking a stab in the dark  as I have used the format function in many of my reports and have had no issues with exporting to excel.

Rob
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Joel MillerDevOps EngineerAuthor Commented:
This did not specifically answer my questions but definitely let me in the right direction. After searching more on that error message, it appears taht if you use an actual datetime value from SQL server in your report and format it in the UI, it still gets sent to Excel as a datetime (with the time and seconds). Having a value with seconds causes an issue with Excel. What I did was open my report in the Data tab and change the three date fields as follows:
CONVERT(varchar(10),[LAST_BILLING_DATE],101) AS [LAST_BILLING_DATE]
This formats the field as text, but in the correct format. The report and the exported Excel document then worked correctly.

Thanks for the help. You definitely led me in the right direction!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.