Solved

Change SSRS date to UK date format

Posted on 2014-04-07
14
5,694 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
14 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39984552
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
ID: 39984645
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
ID: 39986097
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:glennturner1
ID: 39987446
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
ID: 39988003
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
ID: 39988256
I created a textbox and the expression is within it.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39988268
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
ID: 39988272
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
ID: 39991727
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
ID: 39991833
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
ID: 39992007
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
ID: 40001130
Advice provided between two responses did lead to the correct answer, thank you.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

631 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