Solved

Change SSRS date to UK date format

Posted on 2014-04-07
14
4,528 Views
1 Endorsement
Last Modified: 2014-04-15
Hi,

I have read a number of the other questions regarding this topic but I cant work out how to do what I need.

I am new to SSRS reporting, using Microsoft Visual Studio and have inherited a report that includes the following to display a date:

=FormatDateTime(First(Fields!OrderDate.Value, "OrderHed"),2)

The date is produced in the following format:

Friday, April 07, 2014.

Under properties the number format is dd/MM/yyyy however I cant get the date to appear how I would like which would be the UK date 07/04/2014
 
Could anybody tell me the settings I need to change to achieve that please.

If I have left out any necessary information please say.

Thank you
1
Comment
Question by:glennturner1
  • 6
  • 5
14 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
Can you change it in your sql query?

select convert(DATETIME, getdate(), 103)

More info on the sql formats:
http://www.blackwasp.co.uk/SQLDateTimeFormats.aspx
0
 

Author Comment

by:glennturner1
Comment Utility
I just discovered in your link that the 2 in the SQL query means ANSI date, so I changed it to a 3 for UK date, but it just produced 12:00:00 AM instead!

I have found an order date in another report that looks like this and produces the type of date I want ie. 07/04/14:

=iif(isnothing(Fields!OrderDate.Value), "", FormatDateTime(Fields!OrderDate.Value, 2))

Can I modify this one somehow so that it will work in the first  report?
0
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 500 total points
Comment Utility
In SSRS you've got plenty of options to get those dates formatted as expected.  The FormatDateTime function does not need to be used if the Format property is used, and vice versa...

I'd use either the Format property, or the Format function.  Here's what the Format function expression would look like:

=Format(First(Fields!OrderDate.Value, "OrderHed"), "dd/MM/yyyy")

Open in new window

Have a read through following article, I believe it should help you to understand how the formatting works: Formatting Dates [SSRS]
0
 

Author Comment

by:glennturner1
Comment Utility
Hi,  

The expression you used above just produces the result on the report "dd/MM/yyyy"

Sorry, I am new to this, am I missing something I should have put in in place of "dd/MM/yyyy" ?
0
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
Erm, that's weird?  Where did you put that expression?  It should be put as content of the textbox, so not in the Format property...  Is that what you did?
0
 

Author Comment

by:glennturner1
Comment Utility
I created a textbox and the expression is within it.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
What happens if you use the following expression?

=Format(Now(), "dd/MM/yyyy")

Open in new window

0
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 500 total points
Comment Utility
Are you sure the data type of your OrderDate is date or datetime?  If it isn't you indeed get that format string as result (bit strange, I'd have expected an error instead...)
0
 

Accepted Solution

by:
glennturner1 earned 0 total points
Comment Utility
If I use =Format(Now(), "dd/MM/yyyy") it does indeed print today's date in the correct format.

A colleague has worked out that the following produced the result I was looking for:

=Format(Cdate(First(Fields!OrderDate.Value, "OrderHed")), "dd/MM/yyyy")

If that has been suggested by someone, I missed it and I need to award the points please let me know.
1
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
The CDate function converts your OrderDate field to the date type so it confirms what I stated in comment #a39988272.

Disregarding the conversion, the final solution you've mentioned is using the method first described in comment #a39986097.

So a possible closure is your last comment as accepted with the two comments above as assisted...
0
 

Author Comment

by:glennturner1
Comment Utility
OK, could you tell me how to mark two comments as accepted andhow I should plit the points please.
0
 

Author Closing Comment

by:glennturner1
Comment Utility
Advice provided between two responses did lead to the correct answer, thank you.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now