Month and date wrong way around

Hi,

I am querying a report that was written with reporting services, via a webpage in vs2005. However when I input a date field to query from i.e 14/01/2007, this produces an error because the report is seeing this as 01/14/2007, even though in the database the record shows 14/01/2007? Please can someone offer any advice what to check?

thanks,

Harry.

Is that report runs client side?

|||

It seems to happen when report is tested in 'preview mode', and when the report is deployed it happens on the client. An error results because it can't handle the DD/MM the wrong way around?

There must be something I'm missing here...?

Many thanks,

Harry

|||

Hi, Harry:

You should try to format your date before you using them.

You can check out this article about how to format the date in SSRS.

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

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

Hi,

I'm a little confused how I can use the conversions,

I'm passing StartDate AND EndDate from 2 txtboxes,, my data is SELECT par1, par2, par2 from tbldatabase1 WHERE par1 >@.StartDate and par1 <@.EndDate.

But the parameters from the date boxes are taken in the wrong wat around. Do you know how I can implement some code to change this?. My main visual studio pages are written in c#?.

Or would you do this in the query itself?

Any help would be greatly appreciated.

Thanks Harry.

|||

HI,camper :

If your referring toReport Manager displaying the DateTime in theparameter input box, then no, you cannotformat this. You can onlyformat the date within thereport itself, or like the example I posted as following, give the user a dropdownparameter list of dates.

Apparently you can set theparameter to a string and then it won't enter the time, though you'll have to convert it into a date before it runs against your dataset. This way though,report users can enter a non-date as aparameter.

For some of myreports I create aparameter dataset based on the table holding the dates.

SELECT DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField) AS Label, MyDateField AS Value
FROM MyTable
GROUP BY DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField), MyDateField
ORDER BY MyDateField

This creates 2 fields, 'Label' which is what the user selects from and 'Value' which the dataset uses to query on.
Create aparameter (@.MyParameterDate) and reference it against your main dataset e.g.

SELECT * FROM MyTable WHERE MyDateField = @.MyParameterDate

In yourReportparameters set the values to be from a query and select yourparameter dataset. Set the Value and Label datafields to the ones created above and ensure theparameter datatype is DateTime. You can change the aboveformat if you don't want dates displaying as '7 November 2005'

I hope the above information will be helpful. If you have any issues or concerns, please let

me know. It's my pleasure to be of assistance

|||

Hi,

I know it's a few months since the last entry but I was having the same problems and fixed it by setting the Report properties: Language Property to my locale (English (United Kingdom) in this case). You can get at these properties from the report designer (in local mode) and clicking on the grey area outside the design grid. This stopped the report from swapping the months and days around :-)

Good luck.

|||

Change this line in your model's smdl file

<Culture>de-DE</Culture>

In this case German yyyy.mm.dd

|||

I am having the same problem. I saw your solution and tried that. However, it appears that those settings do not get passed to the report server. The report now correctly allows entry of the date in the Visual Studio designer, and when selected shows the date correctly, however, when the report is delpyed, the server, does not change behavior. It still thinks it is using mm/dd/yyy format.

Any ideas how to change the server side format to allow dd/mm/yy parameter format entry?

|||I also had to change regional settings to YYYY/MM/DD format on server. In layout view click properties on righthand side and select report from available fields, Click on language and choose English South Africa for YYYY/MM/DD format.sql