SSRS String to Date conversion (mmddyyyy)-CodePudding

I have a String field in a Dataset in (mmddyyyy) format.

I am trying to convert it into a Date field in SSRS.

I already tried using the below command but I am getting error.

CDate(Fields!LocalTXNDate.Value)

Can anyone please suggest.

CodePudding user response：

The problem here isn't SSRS but your data, and that you are using a string based data type to store the data. You need to fix the problem at the source, not at the report level.

The string format you have chosen, MMddyyyy isn't a format that is recognised by default in any of the languages in SQL Server, nor if you explicitly use SET DATEFORMAT, nor does it appear as a

For the Expression, use string functions to parse the field into a generic date format and then CDATE to convert to a date type. Then use the new field for dates. You could also use this in your text box if it's not being reused but it's easier to manipulate the Calculated field.

=CDATE(
    RIGHT(Fields!LocalTXNDate.Value, 4) & "-" & 
    LEFT(Fields!LocalTXNDate.Value, 2) & "-" & 
    MID(Fields!LocalTXNDate.Value, 3, 2)
    )