What is the correct expression to use in order to format a date from "yyyyMMdd" to "M/d/yyyy" in SQL Server Reporting Services?

Question

I get dates in my dataset in the form "yyyyMMdd" (ie 20080228 means Feb 28, 2008)

I need to convert these to "M/d/yyyy"

Examples:

20080228 = 2/28/2008
20080101 = 1/1/2008
20081001 = 10/1/2008
20081212 = 12/12/2008

what is the correct expression to handle this?

EDIT

The expression that I used (ORDDTE is in the "yyyyMMdd" format and I have a switch for spanish or english date format):

=CDate(Mid(First(Fields!ORDDTE.Value, "ReturnTagHeader").ToString(), 5, 2) + "/" + Right(First(Fields!ORDDTE.Value, "ReturnTagHeader").ToString(), 2) + "/" + Left(First(Fields!ORDDTE.Value, "ReturnTagHeader").ToString(), 4)).ToString(IIf(Parameters!Language.Value = "ES", "d/M/yyyy", "M/d/yyyy"))

Answer 1

If you set an expression on the field you should be able to wrap it in a FormatDateTime()

Example: formatdatetime(FieldWhatever, 0)

The 0 should be the specifier for the particular format you are looking for.

Answer 2

Give this a shot. You can use the string functions. I'm guessing that this input/field/param is always going to have a 2 digit month and 2 digit day, so you can get away with this... Set the expression on the date field as such:

=MID(Fields!ORDDTE.Value.ToString(), 5, 2) 
  + "/" + RIGHT(Fields!ORDDTE.Value.ToString(), 2)
  + "/" + LEFT(Fields!ORDDTE.Value.ToString(), 4)

You could also try to do something like this... found here...

=System.DateTime.ParseExact(Fields!ORDDTE.Value,"dd/MM/yyyy",System.Globalization.DateTimeFormatInfo.InvariantInfo).ToString("yyyyMMdd")

Answer 3

In addition to the Report Services expressions, you can also format the date from the stored procedure:

select 
    CONVERT(VARCHAR(10), [dateField], 101) as 'date' 
from someTable

This has drawbacks in that this will be universal and if your report has to be internationalized, you will be at a disadvantage.

Answer 4

Try this:

Format(FieldWhatever,"MM/dd/yyyy")

Answer 5

Why don't you cast the field to a datetime in your sql call in the dataset.

Select CAST(DateField AS datetime) As Date FROM DateTable

Then for your expression:

=Cdate(Fields!Date.Value).ToShortDateString

Answer 6

Late answer...

Why not set the "Report language" to "User!Language" as mentioned here? This detects browser settings.

Then "d" for short date. Use CDate if needed.