[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How can I set SSRS 2005 date formats to DMY instead of MDY

Posted on 2011-09-05
13
Medium Priority
?
721 Views
Last Modified: 2012-05-12
Hi,
I have SQL 2005 running on a Windows 2003 R2 Server.
I am trying to use the Reporting Services but have found an annoying issue around dates and time.
Basically, SSRS is converting all dates to MDY like the US way but we use DMY for every other part of our environment.

So here are the diagnostic steps I have taken;
1) Set regional settings of the 2003 Server to DMY format.
2) Set the default language of the report to "<Language>en-GB</Language>"
3) Confirmed that the database itself is storing the date as DMY

There is one setting that I have found that is still set to MDY.
If I run "sp_configure 'default language'" on the SQL Server it is set to '0' which indicates English US language. I can set this to British-English but im not sure of the implications of doing this on a server that is already in use. I have tested it on our test server and it didn't seem to affect the live data. What does this setting do? Will it affect the dateformat for Sql Reporting Services? Has anyone had experience in changing this setting on a production server?

Or fundamentally, is there an easier way to change reporting services to DMY?

I know this question has kinda been asked elsewhere but more often the circumstances are different.
0
Comment
Question by:jenkinsgroup
[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
  • 6
13 Comments
 
LVL 2

Assisted Solution

by:awarren85
awarren85 earned 1500 total points
ID: 36486169
Hi,

In my environment (completely USA-based), simply changing, under Report Properties, the Language from "English (United States)" to English (United Kingdom)" changed my formatted dates from mm/dd/yyyy to dd/mm/yyyy.  The dates were formatted in MSRS using the "d" formatting option.
0
 

Author Comment

by:jenkinsgroup
ID: 36486182
Thanks awarren85,
Where is "Report Properties"?.
If I click 'Properties' inside the report after browsing to it through http://***servername***/reports I don't have an option for language.
If I click Report Properties in Visual Studio 2005 whilst editing a particular report .rdl file it doesn't have a setting for language.

Am I missing something?
0
 
LVL 2

Accepted Solution

by:
awarren85 earned 1500 total points
ID: 36486198
It's kind of goofy, and I'm sure there is a better way, but I don't know it.  See the screenshot attached and it's how I access the Properties.  In the screenshot, click the location where the black box is in the top-left, then you can change "Report Properties"  Thanks.
msrs-edit-prop.JPG
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:jenkinsgroup
ID: 36486309
Thanks awarren85 but I had already set that setting via the XML page. <Language>en-GB</Language>
Unfortunately the date is still converting to MDY.
0
 
LVL 2

Expert Comment

by:awarren85
ID: 36486329
Huh, freaky.  That was enough on mine in both the Visual Studio Preview tab AND when deployed to the http://*servername*/reports folder to go to D/M/Y.  Can you verify that you're formatting the dates using the "d" option?  May want to as well verify that the data coming out of the database is in datetime format.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36486775
see image
Untitled.png
0
 
LVL 2

Expert Comment

by:awarren85
ID: 36488712
@PatelAlpesh:That looks like MSRS 2008, unfortunately the poster is on MSRS 2005.  I've attached the screenshot from 2005 that shows the textbox formatting, set to "d" in this example.
msrs-2005.JPG
0
 

Author Comment

by:jenkinsgroup
ID: 36492076
Hi guys, thanks for your responses.
Ive made some steps forward in debugging this problem.

The problem lies not with the report or with the report server even!
I have discovered that when im on the database itself and do a query like this;
select * from table where date < '13/05/2011'
it fails saying invalid datetime. Obviously if i change the query to;
select * from table where date < '05/13/2011'
it works!
The annoying thing is that the dates are being written and stored on the db in DMY.

OK so, I changed the sp_configure 'default language' of the database to '23' (British English) but that hasn't helped.

Anyone know how to change the way sql queries the dates?

Should I relist this query under a new question?
0
 
LVL 2

Expert Comment

by:awarren85
ID: 36493378
Take a look at this article, looks like it's a SQL Server setting:

http://msdn.microsoft.com/en-us/library/ms189491.aspx

Thanks.
0
 

Author Comment

by:jenkinsgroup
ID: 36498986
Mmm, this doesn't work in a view for me.
The data for my reports is coming from a view.
0
 
LVL 2

Expert Comment

by:awarren85
ID: 36499142
This should change the way the dates are handled in the where clause, for instance on my test box:

set dateformat dmy
go
select mat_date from dvfidel.jk.fr where MAT_DATE < '12/31/2010'

 (where mat_date is a datetime), throws an error that the string could not be converted to a datetime value.  However, if I do:

set dateformat mdy
go
select mat_date from dvfidel.jk.fr where MAT_DATE < '12/31/2010'

it works, and successfully converts the date.

0
 

Author Comment

by:jenkinsgroup
ID: 36519772
Hey guys sorry took so long to reply.
NZ shutdown last week due to the world cup so I wasn't at work.

Basically I've made a workaround fix here.

I found that if I set the report parameter to a string instead of datetime it would accept the english time. strange. but hey its working now.

Thanks awarren85 for your help.
0
 

Author Closing Comment

by:jenkinsgroup
ID: 36519778
As said in comment above.
awarren partially solved this issue. I had to set the report parameter to string.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

656 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