SSRS Reports: Date format changes when exported to .csv

sravya shivapuram 211 Reputation points
2022-04-20T17:48:14.07+00:00

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.

194863-image.png

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

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,873 questions
0 comments No comments
{count} votes

Accepted answer
  1. Joyzhao-MSFT 15,571 Reputation points
    2022-04-21T03:59:04.693+00:00

    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].
    194808-image.png
    Then I set the expression in Table as follows:

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

    194981-image.png

    Preview:
    194858-image.png
    It also works fine when exporting the report to .csv format.
    194962-image.png
    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.


2 additional answers

Sort by: Most helpful
  1. Isabellaz-1451 3,616 Reputation points
    2022-04-21T02:13:33.48+00:00

    Hi @sravya shivapuram

    How about change the format like this:
    194889-image.png
    Preview result:
    194921-image.png
    output csv file:
    194931-image.png

    Best Regards,
    Isabella


  2. sravya shivapuram 211 Reputation points
    2022-04-21T14:27:49.687+00:00

    Hi @Joyzhao-MSFT ,

    Thank you for your response. That worked. :)

    Appreciate all your time and help.

    Regards
    Sravya

    0 comments No comments