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 reportCAST(@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 toooothese 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 returnsfroom = '01/06/2006' the 1st of june 2006toooo = '31/03/2007' the 31st of March 2006now when the report is deployed and run on the server the above returnsfroom = '06/01/2006' 6th of January 2006toooo = '02/02/2007' 2nd of Feb 2007so 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?
March 6th, 2007 1:02pm
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.. :( //Pr
March 6th, 2007 3:44pm
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
March 6th, 2007 4:38pm
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
March 6th, 2007 4:48pm
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.
March 6th, 2007 9:21pm
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?
March 7th, 2007 11:43am
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
March 7th, 2007 12:37pm
The report is currently set to English (United Kingdom). Changing it to "=User!Language" makes no difference...
March 7th, 2007 12:43pm
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.. //Pr
March 7th, 2007 12:53pm
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
March 7th, 2007 1:12pm
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 serverdesigner: http://img185.imageshack.us/img185/7969/untitled2ez6.jpgserver: http://img242.imageshack.us/img242/2125/untitled3gp4.jpg
March 7th, 2007 1:35pm
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
March 7th, 2007 1:52pm
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?
March 7th, 2007 1:54pm
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.
March 7th, 2007 2:08pm
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 itis 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 ShyamIts 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
March 7th, 2007 2:13pm
Can you try just printing the date as it is first without any calculations or modifications done to it? Check if 31 March comes back as 31 March atleast because I think User!Language is used only for display purpose but not for calculations.
March 7th, 2007 2:30pm
Try setting the language of your browser (Internet Explorer) appropriate to the server and generate the report from Report Manager. Tools -> Internet Options -> Languages (button) -> Remove all existing and add the appropriate one Shyam
March 7th, 2007 2:52pm
we have 2 dates the first is not processed in anyway and just displayed the second has this add a month and subtract a day to find the end of the month...Now when i set the first date to equal to 1st April 2007 it is displayed as 04/01/2007and the second date which is calculated equal to 1st September 2007 is displayed as 08/02/2007
March 7th, 2007 4:48pm
Nobble wrote:we have 2 dates the first is not processed in anyway and just displayed the second has this add a month and subtract a day to find the end of the month...Now when i set the first date to equal to 1st April 2007 it is displayed as 04/01/2007and the second date which is calculated equal to 1st September 2007 is displayed as 08/02/2007so it looks like i was wrong in my inital assessment of the problem.. they server is taking the dates supplied and treating them and displaying them as US dates and not UK dates as it should
March 7th, 2007 5:27pm
That is, we have the exact same problem?
March 7th, 2007 5:37pm
so does anyone else have any ideas or a possible solution to this?The report is set to '=User!Language', the browser viewing the report is set to en-UK yet dates are still processed as if they were US?
March 8th, 2007 10:56am
i hate to be spamming my own thread, but i really need a solution to this, has anyone else come across this or have a solution for it?
March 9th, 2007 5:31pm
As Reporting services is installed in US-English version on your server, it will always take the incoming date as mm/dd/yyyy format. You may have to write your own cases for different languages where you have to know which language uses which format. For example in your report, you can use the following expression to interpret the date based on the client's language which is the browser in your case: IIf(User!Language = 'en-UK', Datepart(DateInterval.Day, Fields!YourDate.Value) & "/" & CStr(Month(Fields!YourDate.Value)) & "/" & Cstr(Year(Fields!YourDate.Value)), Fields!YourDate.Value) OR you can write allsuch cases in a public method in a utility dll and refer that dll and call the method to get the US date format from the client's date format. Hope this helps. Shyam
March 12th, 2007 2:28pm
Is there a way to change Reporting Services version? i.e. change it from en-US to en-UK....
March 12th, 2007 6:26pm
I guess not
March 13th, 2007 10:33am
I have ran into the same problem last week. The problem occurred after installing Vista and SQL 2005 SP2. The dates were being switched when running a report. (ie. June 1st, 2006 was being changed to Jan 6th, 2006.) I did a series of things which seemed to solve it. I had changed my local date/time to Month/day/year as well, I also received the latest updates of Vista. For some reason, it was solved after that. I haven't ran into that problem again.
March 20th, 2007 5:10pm
well after further investigation it appears that Reporting services can only be installed in en-US format... so still at a loss to why this sort of bug/error occurs
March 20th, 2007 7:09pm
So, can you mark my post as answer? Shyam
March 21st, 2007 10:02am
yes, as it is sort of an answer to this problem just not the answer i was really looking for.......
March 21st, 2007 11:09am
The answer you are looking for has to be provided by MS in their next versions. As developers, we can only look for workarounds and solutions within whatever is available. Shyam
March 21st, 2007 11:45am
There must be a way to actually avoid this problem by changing some sql server or reporting services settings. If there is not then this could be the last straw for me, i.e. it might be time to reconsider other reporting packages.
November 2nd, 2007 9:11pm
You may have considered this, but the easiest way to change the language setting for all of your reports at once from Visual Studio is to do a find replace on your reports project and replace the <Language>en-US</Language> lines in the XML code of your reports with <Language>en-AU</Language> This will update hundreds of reports in seconds. Of course test it out with one or two reports first before doing your whole project to make sure you have got your find & replace strings right.
November 29th, 2007 9:25am
A possible solution is the following. You only have to do this once: Change the localLanguage Setting (Start --> control panel --> Regional and Language Options) to the language you prefer. Or change the DateFormat to wich you prefer. In some cases it's still possible the problem occures. But it helped for me anyway. Good luck!
December 5th, 2007 1:35pm
I have had this date issue on a couple of installations of Reporting Services. To force the entire RS installation to you local Culture setting you need to add a Culture tag to the ReportViewer.aspx file which is located in the ..ReporterServer/Bin folderonyou server which hosts RS. You add the tag to the'<%@ Page Language' lineas below: <%@ Page Language="C#" AutoEventWireup="true" Inherits="Microsoft.ReportingServices.WebServer.ReportViewerPage" Culture="en-AU" %> In my case I had to force it to English - Australian, substitue you own culture setting as required. This will force all reports to the Culture setting set over riding any report settings. Hope this helps. Ray
December 17th, 2007 8:22am
I have the same date format problem, for us the server is located in US and users across the world access this server. It gives error as users in different countries have their own date format. I used =User!Language for report language to make it independent. But it is giving error in one particular scenerio For ex : If my browser langauge format is UK .The first time report generates correctly with date format as dd/mm/yyyy , but if I change the date it reverts to US mm/dd/yyyy and if I select a day greater than 12 it fails (gives error reportparameter mismatch). If the day is less than 12 it works and next time if I select a different date in the same report it again reverts to dd/mm/yyyy and from then onwards it works fine for ever. The problem is only for second time where it reverts to US format. I could not find the reason why the report viewer behaveslike this i.e first time correctly , second time wrong and the from third time always correct. Any clues will be of great help
April 3rd, 2008 10:28am
I am not sure if this is your issue but there is a know bug with the datetime picker always showing US date format after installing SQL 2005 SP2. See post.... http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3096820&SiteID=1
April 3rd, 2008 11:09am
Anyone know if this bug/issue was fixed in the release of SQL 2008?
May 20th, 2008 10:08pm
I've got the same problem, I'm based in Australia and have problem getting reports to display dd/MM/yyyy properly. From the advice given so far on this post I've done the following: 1) Checked that my regional/language settings are set to en-AU 2) Put the en-AU culture tag in the ReportViewer.aspx page 3) Manually altered the report rdl language tag to en-AU. Still can't get it to work. the report dataset is a dynamic SQL statement, in which I allow free text "search criteria" build up a where clause against a view to return a sub-set of all the possible report data. Is there anything else I'm missing? Is there a region/culture setting within SQL server itself I should be changing as well. I'm thinking that because I'm dynamically constructing my query that all well intentioned regional settings are being ignored anyway. Cheers Jasper
June 10th, 2008 10:29am
Have you tried the hotfix? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3096820&SiteID=1
June 10th, 2008 1:57pm
Hi, I'm facing the same problem as Aparna, i tried all the manual ways mentioned above, they didn't work. (In my case one user is able to see the report, the others, when they put a day above 12 they get an error). I need to know if anybody faced this issue and solved it and if the hotfix would work for me. Thanks in advance
July 24th, 2008 11:07am
I know it is an old post but I tried all the suggestions above and none worked for me. But this expression outputted date in UK format (dd/mm/yyyy): = FormatDateTime(Fields!Date.Value,2)
January 19th, 2011 7:14am