SSRS Date format from YYYYMMDD to MM / DD / YYYY

Question

When I run the report, the date is displayed in the format YYYYMMDD , but I need to convert it to the format MM/DD/YYYY

For example: 20150213 - expected result 02/13/2015

The expression below does not work. Suggestions?

 =Format(Fields!InstallDate0.Value,"MM/dd/yyyy") 

Answer 1

InstallDate0 be a string, not a date. First convert it to a date, then format it:

 =Format(CDate(Fields!InstallDate0.Value), "MM/dd/yyyy") 

Answer 2

Is the field stored in the database as a DateTime field or as something else?

I assume this is not a DateTime , but an integer or a string.

Assuming your column is called InstallDate0 , change your query to use this expression (we will assume that the source table has an alias of T1 ):

 InstallDate0 = CONVERT(datetime, CONVERT(varchar(8), T1.InstallDate0), 112) 

This will return your string (or int) as a DateTime. Now you can use the SSRS formatting features:

 =Format(Fields!InstallDate0.Value,"MM/dd/yyyy") 

Answer 3

You should be able to use:

 =Format(Fields!InstallDate0.Value,"d") 

If this does not work, I assume that you wrap the date in the report as a string. You can format the string YYYYMMDD in datetime format in sql query:

 cast(InstallDate0 as datetime) 

Answer 4

It worked for me -

Format(CDate(DateAdd(DateInterval.DayOfYear, 1, Now())), "MM/dd/yyyy")

It was required to get the current date + 1 in the format MM / dd / yyyy. You can replace DateAdd(DateInterval.DayOfYear, 1, Now()) with a field value.

Answer 5

I found out that you can directly define user settings in your cell settings like this:

 MM'/'dd'/'yyy 

Answer 6

A quick solution that might help someone, in my case, @ini_date is a parameter, so query conversion is not applicable ....

When the date is in this format → yyyymmdd, I “built” a valid date string using right () mid () and left (), combine it with a slash, and then convert it to a date type, so it can be formatted.

Here is the expression →

 ="Date: "&format(cdate((right(Parameters!ini_date.Value,2) &"/"&mid(Parameters!ini_date.Value,5,2)&"/"&left(Parameters!ini_date.Value,4))),"MMMM dd, yyyy") 

just replace the “MMMM dd, yyyy” part with the one that best suits your report.

Answer 7

This can help.

Let's say InstallDate0 = 151116 (just replace InstallDate0 with whatever your name is!)

I want to convert the following 151116 => 11/16/2015

yyMMdd type strings

to

MM / dd / yyyy type dates

Expression: =format(CDate(Mid(Fields!InstallDate0.Value,3,2)&"/"&Right(Fields!InstallDate0.Value,2)&"/"&Left(Fields!InstallDate0.Value+20000000,4)),"MM/dd/yyyy")

if you want to do the same except from 20151116 => 11/16/2015

yyyy MMdd string type

to

MM / dd / yyyy type dates

the expression will be: =format(CDate(Mid(Fields!InstallDate0.Value,5,2)&"/"&Right(Fields!InstallDate0.Value,2)&"/"&Left(Fields!InstallDate0.Value,4)),"MM/dd/yyyy")

Tags: how to convert string to date with expression for SQL Server Report Builder