CODEWITHSUNDEEP
sql-serverreporting-services
bapi
bapi

Reputation: 1943

Display date in MM-dd-yyyy format SSRS report

I have a SQL query: select ModifiedDate from Person.Person and this returns the date as 2/24/1998 12:00:00 AM

I'm trying to display this in MM/dd/yyyy format in SSRS report. I have used the expression =Format(Fields!ModifiedDate.Value,"MM/dd/yyyy")

But, still it's coming as 2/24/1998 12:00:00 AM

I want to display this as 2/24/1998.

How can I do this?

Upvotes: 19

Views: 124430

Answers (8)

n34_panda
n34_panda

Reputation: 2677

I would recommend using the format codes:

Right click - properties on the cell, select format, click the ellipsis "...", and you can see the date formats from there. This will be converted into a date code when you OK the dialog. This is useful as it sets the date in the fomat the user wants to see it in.

To convert the data within SSRS and not the data source you could try using something like:

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

Another sample for you without Cdate:

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

Upvotes: 24

Zaki
Zaki

Reputation: 37

You can use:

- FormatDateTime(Fields!ModifiedDate.Value, 2)

or:

- FormatDateTime(Fields!ModifiedDate.Value, DateFormat.ShortDate)

You will get the same result.

Upvotes: 1

saharghodsbin
saharghodsbin

Reputation: 91

I had this problem and i solve that by this code :

FormatDateTime('MM/dd/yyyy',ADOqueryname.fieldbyname('ModifiedDate').AsDateTime);

Upvotes: 2

Aparat
Aparat

Reputation: 133

There is a special function for formatting Dates in SSRS:

=FormatDateTime(Fields!ModifiedDate.Value, DateFormat.ShortDate)

It will return your date the way that you want it.

Upvotes: 11

bapi
bapi

Reputation: 1943

I did two things to make it work:

1st, In Data set property Query - select CONVERT(varchar(20),ModifiedDate,101) from Person.Person

2nd, In expression - =Format(Fields!ModifiedDate.Value,"dd/MM/yyyy")

This worked for me.Thanks for the hints.

Upvotes: 1

Pedram
Pedram

Reputation: 6508

As per your question,

To convert the date in SSRS you should try like this,

=FORMAT(Fields!ModifiedDate.Value,"MM-dd-yyyy")

Output would be,

12-06-2010 -- 12(Month)-06(Date)-2010(Year)

Upvotes: 6

Marylyn
Marylyn

Reputation: 41

Set the language of the entire Report to the language of the country the report is going to be run in.

For example for US it's en_US and for Australia it's en_AU.

Simple !

Upvotes: 3

Pranav Bilurkar
Pranav Bilurkar

Reputation: 965

If you want to get date in format of 'MM/DD/YYYY' use the following query andand you have to convert in varchar datatype. 

select CONVERT(varchar(20),GETDATE(),101)

Upvotes: 2

Related Questions