Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6316
  • Last Modified:

Change SSRS date to UK date format

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
glennturner1
Asked:
glennturner1
  • 6
  • 5
3 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
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
 
glennturner1Author Commented:
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
 
ValentinoVBI ConsultantCommented:
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
glennturner1Author Commented:
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
 
ValentinoVBI ConsultantCommented:
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
 
glennturner1Author Commented:
I created a textbox and the expression is within it.
0
 
ValentinoVBI ConsultantCommented:
What happens if you use the following expression?

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

Open in new window

0
 
ValentinoVBI ConsultantCommented:
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
 
glennturner1Author Commented:
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
 
ValentinoVBI ConsultantCommented:
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
 
glennturner1Author Commented:
OK, could you tell me how to mark two comments as accepted andhow I should plit the points please.
0
 
glennturner1Author Commented:
Advice provided between two responses did lead to the correct answer, thank you.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now