Nobble
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
Re: Reporting Services Date Format Problem
Par Hedberg
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.. :(
//Par
Re: Reporting Services Date Format Problem
Nobble
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
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
Re: Reporting Services Date Format Problem
Trond Brande
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
Re: Reporting Services Date Format Problem
paulie_m
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.
Re: Reporting Services Date Format Problem
Nobble
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
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
Re: Reporting Services Date Format Problem
a_shyam41
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
Re: Reporting Services Date Format Problem
Nobble
The report is currently set to English (United Kingdom). Changing it to "=User!Language" makes no difference...
The report is currently set to English (United Kingdom). Changing it to "=User!Language" makes no difference...
Re: Reporting Services Date Format Problem
Par Hedberg
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..
//Par
Re: Reporting Services Date Format Problem
a_shyam41
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
Re: Reporting Services Date Format Problem
Nobble
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
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
Re: Reporting Services Date Format Problem
a_shyam41
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
Re: Reporting Services Date Format Problem
Par Hedberg
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
Re: Reporting Services Date Format Problem
a_shyam41
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.
Re: Reporting Services Date Format Problem
Nobble
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
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