Reporting Services Date Format Problem

I have a huge problem with reporting services..

For any reports I am running it interprets all of my dates as US format.

for instance I'm using the following code snippet to specify the date range of a report

CAST(@.FromYear + '-01-' + @.FromMonth AS DATETIME) AS froom MIN(allpartmaster.snlcat) AS cat, DATEADD(dd, - 1, DATEADD(mm, 1, CAST(@.ToYear + '-01-' + @.ToMonth AS DATETIME))) AS toooo

these are then used to not only limit the sql statement's return set but also as labels on the report for clairfication.

parameters are of string type but are passed the likes of

@.FromMonth = '06'

@.FromYear = '2006'

@.ToMonth = '03'

@.ToYear = '2007'

Now I run the report on my developemtn machine with out any problems the above returns

froom = '01/06/2006' the 1st of june 2006
toooo = '31/03/2007' the 31st of March 2006

now when the report is deployed and run on the server the above returns

froom = '06/01/2006' 6th of January 2006
toooo = '02/02/2007' 2nd of Feb 2007

so the dates are being displayed in the correct format for what they are.. but the actual processing of the reports on the report server is flipping them to US format initially and then back to UK format to display them.

I'm at a loss to understand why.. my browser's language settings are UK and my server's windows regional settings at UK as well...

does anyone have any ideas as to why this is happening?

I have no solution, but am experiencing a similar problem..

Using swedish datetypes in SQL Server (2007-03-06), a report will always display it in US format (06/03/2006)..

On every SQL query i use, i have to do a "convert(char(10),date,120) as date" to get it into right format..

The problem is when the datasource is an OLAP cube.. :(

//P?r

|||ok been looking into this further....

unlike your problem my dates are being displayed in the correct format i.e. en-GB but they are being processed by the reporting server as en-US dates. Now I've been looking at the log files for reporting services on my server and the locale is listed at the top of the log file as en-US thus :

<Header>
<Product>Microsoft SQL Server Reporting Services Version 9.00.2047.00</Product>
<Locale>en-US</Locale>
<TimeZone>GMT Standard Time</TimeZone>
<Path>path removed by nobble</Path>
<SystemName>server name removed by nobble</SystemName>
<OSName>Microsoft Windows NT 5.2.3790 Service Pack 1</OSName>
<OSVersion>5.2.3790.65536</OSVersion>
</Header>

so it looks like i have to change the report server's locale... any one know how to do this
|||

This is the same issues I have (in the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1309233&SiteID=1)

Changing local for Reporting Services should do the trick - changing any other local settings sure does not seem to help.

I'll post back to this thread if I get any answers on my thread...

Trond

|||

We have just come up against this problem as well. I think it has only started since we installed SQL 2005 SP2, but I cannot be sure.

Certainly there have been no complaints for the past 6 months, we installed SP2 last Friday and the complaints started today.

|||Well I installed SP2 in a hope that it would fix it, initially on my development machine and was going to install it on my server today... glad you've saved me the trouble..

This seems to be a pretty fundamental problem with Reporting Services has no one else got a work around or solution?|||

Go to the Properties dialog of the Report and for Language property try setting the value "=User!Language" instead of English (United States) in which case you dont need to do anything in the sql server query other than just retrieving the date in whatever format it is in the database.

Shyam

|||The report is currently set to English (United Kingdom). Changing it to "=User!Language" makes no difference...
|||

a_shyam41 wrote:

Go to the Properties dialog of the Report and for Language property try setting the value "=User!Language" instead of English (United States) in which case you dont need to do anything in the sql server query other than just retrieving the date in whatever format it is in the database.

Shyam

This might solve my problem, but there has to be a way to make this a standard value (or to set the server to the right locale setting)..
There is no way i want to do this for hundreds of reports already made, and have to remember it for the hundreds of more that are about to be made..

//P?r

|||

This should solve the problem.

I am almost sure that there is no way to do it at a server level as I myself have developed 100s of globalized reports and this was my first item in the checklist.

Shyam

|||Well i've tried it again and it is still causing me a problem.. the report is now set to "=User!Language"... it works in the designer but not on the server

designer: http://img185.imageshack.us/img185/7969/untitled2ez6.jpg

server: http://img242.imageshack.us/img242/2125/untitled3gp4.jpg|||

As far I can see, the reports are not the same on your local machine (designer) and the server (which may be again your machine which I dont know).

If it is same and my solution didn't work, then the second date in the report server should have shown 03/31/2007. I dont know why it's showing 02/02/2007

Shyam

|||

a_shyam41 wrote:

This should solve the problem.

I am almost sure that there is no way to do it at a server level as I myself have developed 100s of globalized reports and this was my first item in the checklist.

Shyam

And there is no way to make Visual Studio set that as a default?

|||

check out this link

http://msdn2.microsoft.com/en-us/library/ms156493.aspx

and also this paragraph in the link:

"For Report Designer itself, the language resource that is applied to the report-authoring environment is determined by the language of the installation of Microsoft Visual Studio. For example, if you are running a Japanese version of Visual Studio, the Japanese language resource for Report Designer is used. If you are running a language version of Visual Studio that is not supported by SSRS, the neutral resource language is used instead. For more information about neutral resources, see "Operating System Language Settings" later in this topic."

So, the Language property value that you see in the Visual Studio report designer is actually the language of Visual Studio installtion. So, nothing can be done about it but to handle them manually for every report.

|||

a_shyam41 wrote:

As far I can see, the reports are not the same on your local machine (designer) and the server (which may be again your machine which I dont know).

If it is same and my solution didn't work, then the second date in the report server should have shown 03/31/2007. I dont know why it's showing 02/02/2007

Shyam

Its the same report deployed on the server, which is a different machine to my development machine.

Its showing 02/02/2007, because its taking the date passed to it 2007-01-03 as the 3rd of Jan not the 1st of March, so when it adds a month and takes away a day it gets the 2nd of Feb, which it then displays as 02/02/2007. As I said the displaying of the date in the report is fine, its the actual calculation that is incorrectly converting it into en-US format... This is my problem