Trouble maintaining date format when exporting from ssrs to excel

0
votes

I am using SSRS to extract data from Oracle - this includes a datetime field called 'created' - when I generate an excel file from ssrs, I want the date field to show in Excel as custom dd-mmm-yy - however it currently shows as general (in Excel, right click on field, field properties)

I've already tried in report builder:

Changing the Number Format to 'N'

=CInt(Fields!FieldValue.Value)

Changing the Number Format to dd-MMM-yy

In SSRS report builder I have created the field 'created' in my SQL query as follows:

upper r(to_char(UNI72LIVE.PR_BLPU.CREATED_D,'dd-mon-yy')) as Created

This displays correctly in report builder, but the problem occurs when the field is viewed in Excel

2
exceloraclereporting-services
As an aside to your question, there is not reason to use the UPPER function the way you have. Just make the "mon" portion of your to_char format mask in upper case: to_char(UNI72LIVE.PR_BLPU.CREATED_D,'dd-MON-yyyy'). Also, please use 4-digit years. Armies of people like me busted our butts over this 20 years ago. If you are too young to remember, look up 'Y2K issue'. – EdStevens

2 Answers

0
votes

If you convert the date field to a character string using to_char then Excel will have text in that cell rather than a date and won't be able to treat it as a date for sorting and calculations. Just have the date field as a plain field in your SQL statement so that it remains a date.

In your cell, set the Value property just to the field value (no formatting):

=Fields!CREATED_D.Value

Set the Format property to dd-MMM-yy and the date will be displayed in the format you want.

Now the date field will export to Excel as an actual date value (rather than text) and the format of the date will be set properly.

0
votes

You need to apply the date format in the expression of the textbox,

=Format(CDate(Fields!FieldValue.Value), "dd-MM-yyyy")

AND you need to go to this the textbox properties: Textbox Properties > Number > Date and chose as format Date.

And of course your language have to be set to a value. This way it should work when you export it to Excel.