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
jmiller47Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
shorakConnect With a Mentor Commented:
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
 
jmiller47Author 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
All Courses

From novice to tech pro — start learning today.