SSRS String to Date conversion (mmddyyyy)

Question

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.

Answer 1

While Larnu is correct, the way to do it is to correct the database, sometimes we lowly report makers have no say in making these changes - much less getting a DBA to do it in a reasonable amount of time.

If you can't change the data to be correct, the easiest way to convert and use the field as a date is to add a Calculated Field to the Dataset. Open the dataset properties, click on the Fields tab, Add a Calculated field.

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)
    )

Answer 2

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 style. SET DATEFORMAT MDY; SELECT CONVERT(date,'11172022'); will fail. Therefore you'll need to first do some string manipulation on the data first to be an unambiguous format (yyyyMMdd):

UPDATE YT
SET YourDateColumn = CONVERT(varchar(8),V.DateValue,112)
FROM dbo.YourTable YT
     CROSS APPLY (VALUES(TRY_CONVERT(date,CONCAT(RIGHT(YT.YourDateColumn,4),LEFT(YT.YourDateColumn,4)))))V(DateValue);

For any bad values you have, such as '17112022' this will UPDATE the value to NULL; as such you may want to create a new column for the new value, or perhaps a new column to store the value of dates that couldn't be converted.

After you've changed the value to an unambiguous format, then you can ALTER the column:

ALTER TABLe dbo.YourTable ALTER COLUMN YourDateColumn date NULL;

Note that if you have any constraints, you will need to DROP those first, and then reCREATE them afterwards.

Now that the data type of the column is correct, you need not do anything in SSRS, as the data type is correct.