Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SSRS export to Excel converts DateTime Column to Text Column

Recently we observed a weird issue with SSRS export to Excel. One of the datetime column in the report was formatted using the FORMAT function as “MM-dd-yyyy HH:mm:ss” (24 hrs format). However when this report was exported to Excel, this column was converted as Text column instead of DateTime.

In the below screenshots, the first one depicts the report and the second one is the Excel output of the same.

Image

Image

Note the highlighted in red, the DateTime column is treated as General (Text) not as DateTime, this removes the ability to pivot or apply some formula on that column.

After couple of hours I came across an article (unfortunately forgot the URL :( ) which said that using FORMAT function removes the actual data type of the column while exporting. Instead it suggested to use FORMAT property (as in below screenshot)

Image

That trick worked. Below screenshot is from the Excel export of the report where the formatting was done using the Property and it behaved as expected.

Image

 

 

 


Comments

Leave a comment on the original post [skamie.wordpress.com, opens in a new window]

Loading comments...