SSRS Format date from YYYYMMDD to MM/DD/YYYY

Question

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

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

The expression below does not work. Suggestions?

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

Dichogamy

Comes out where? in a prompt or on the report? How does it not work? Does it throw an error? if not what is the result? – Madison

When I run the SQL query, the results show "20150213" for example. Shows the same thing when I add it to SSRS. There are no errors. I need to convert that format to 02/13/2015. – Dichogamy

@Dichogamy Did you solve your problem? – Tiercel

Answer 1

It looks like InstallDate0 is a string, not a date. Convert it to a date first, then format it:

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

Lipman

I receive #Error in all of the records for that column when using this expression. – Dichogamy

Doesn't work without first converting the string to a date in SQL. The problem for many seeking a solution within SSRS is that they don't have access to the back-end data. – Aslant

Answer 2

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

I'm going to assume that it's not a DateTime but an integer or string.

Assuming your column is called InstallDate0, change your query to use this expression (we're going to assume the source table has the alias T1):

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

That will return your string (or int) as a DateTime. You can now use the formatting capabilities of SSRS:

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

Tiercel

Answer 3

I found out, that you can define directly in the settings of your cell a user-defined setting like this:

MM'/'dd'/'yyy

Paroicous

Answer 4

You should be able to use:

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

If this doesn't work, my guess is that you are bringing the date into the report as a string. You can cast a YYYYMMDD formatted string as datetime in your sql query:

cast(InstallDate0 as datetime)

Goldofpleasure

=Format(Fields!InstallDate0.Value,"d") --- All this did was put "d" in all of the records for that column. cast(IinstallDate0 as datetime) did not work in the query as well. – Dichogamy

error I receive is "Conversion failed when converting date and/or time from character string." – Dichogamy

Can you please provide your sql query? – Goldofpleasure

Answer 5

This worked for me -

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

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

Mcwhirter

It is similar to the first answer. I don't have enough reputation to comment or up vote hence posting as an answer. – Mcwhirter

Answer 6

=CDATE(MID(Fields!LAST_UPD.Value,5,2) + "/" + Right(Fields!LAST_UPD.Value,2) + "/" + Left (Fields!LAST_UPD.Value,4))

Not the prettiest solution, but fast and works fine.

Diorite

This is the one! – Aslant

Answer 7

Quick solution that maybe can help someone, in my case, @ini_date is a parameter, so query conversion was not applicable....

When a date is in this format -> yyyymmdd, I 'constructed' a valid date string, with right() mid() and left(), concatenate it with slashes, and then convert it to date type, so it can be formatted.

Here's 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 whatever fits your report the most.

Aucoin

Answer 8

This might help.

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

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

yyMMdd of type string

to

MM/dd/yyyy of type date

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

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

yyyyMMdd of type string

to

MM/dd/yyyy of type date

the expression would be the following: =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 an expression for SQL Server Report Builder

Engen