Solved

Change SSRS date to UK date format

Posted on 2014-04-07
14
4,745 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 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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 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