SSRS Reports: Date format changes when exported to .csv

Question

Hi,

I have an SSRS report with one of the columns like the following ( this field is defined as a varchar) -

Jan - 23
Feb - 23
Mar - 23
Jan - 24
Feb - 24
Mar - 24

It looks fine in the report viewer and Excel, but not in .csv file. My expectation is that .csv file should consider 'Jan - 23' as 1/1/2023, 'Feb - 23' as 2/1/2023 , 'Mar - 23' as 3/1/2023. But instead, it changes to '23-Jan', '23-Feb', '23-Mar' and assumes the date as 1/23/2022, 2/23/2022,3/23/2022 . How can I make sure that the date format is 'Jan-23' i.e. 1/1/2023 instead of '23-Jan' in .csv file? Please advise.

I set the text box properties to display in the format : mmm-yy but doesn't seem to take effect.

Any help is greatly appreciated. Thank you in advance.

Note: Opened the .csv in notepad++ and it had 'Jan-23', 'Feb-23','Mar-23' etc. Did a trial and error method and noticed that once I modified the notepad as Jan 2023, Feb 2023, Mar 2023, it showed up the way I was expecting i.e Jan-23(1/1/2023).

Regards
Sravya

Answer 1

Hi @sravya shivapuram ,
Since your data type is varchar, there is no way in SSRS to distinguish between "23" or "24" in your date as the year value, nor to automatically recognize the month abbreviation as numbers.
I've done a lot of testing and the closest I can think of to your needs is to manually convert the string to the format we need, i.e. extract the parts of your field that we need.
I created the same table as you in the database, the data type is varchar. The field name is [Date].

Then I set the expression in Table as follows:

=Left(Fields!Date.Value, 3) + "" + "-1"  + "" + "-"+ "" + "20" + "" +  Right(Fields!Date.Value, 2)  

Preview:

It also works fine when exporting the report to .csv format.

Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @sravya shivapuram

How about change the format like this:

Preview result:

output csv file:

Best Regards,
Isabella

Answer 3

Hi @Joyzhao-MSFT ,

Thank you for your response. That worked. :)

Appreciate all your time and help.

Regards
Sravya