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

 

 

 

Advertisements

About Suresh Kumar D

Hardcore SQL Developer and aspiring Database Architect.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s