Reporting Services Calendar Widget for Non-US date format

Question

Post reply

Reporting Services Calendar Widget for Non-US date format

Graham Smith-563576

Valued Member

Points: 53
More actions
November 27, 2007 at 6:00 pm

#184119

I am fairly new to the Reporting Services UI, so I am not sure if this is a small local config issue, or part of a larger problem.
Firstly, I am in Australia, and so my users expect to see dates in dd/mm/yyyy format (btw - I am English so please forgive me if I sometimes refer to it as UK format). I have built a report that uses two dates as parameters within the SQL, as seen in this snippet...
[font="Courier New"]and o.OrderJournalDate
between @FromDate
and dateadd(day, +1, @ToDate)
-- (adds 1 day to get round the 00:00:00 time default, where from and to dates are the same)
[/font]
The SQL code works fine, but the UI seems to have some strange behaviour. Everything on my PC and (as far as I can tell) the SQL Server machine, is set up for Australian English (i.e. timezones, international settings, and so on) and the report UI (i.e. the 'Preview' tab in the BI Development Studio) shows calendar widgets that are correctly showing 'today' at startup. If I select a date such as 28th November from the calendar, it is placed in the parameter box in the form 28/11/2007, which is just what I want.
Now the fun starts...
Running the report is passing the value into @FromDate for example, in US format so the SQL code thinks it has the 11th of TwentyEightber, which obviously causes it a headache... The 1st of November is erroneously treated as 11th of January, and returns the wrong data. The 7th of July works perfectly, but unfortunately, I can't persuade my users to restrict their reports to the 12 days a year that have ambidextrous dates :ermm:
Having got thus far, it gets even weirder...
Assuming for a moment I chose a date that doesn't cause the whole thing to crash in a heap, say the 1st of November (1/11/2007 in UK format) - which returned data from 11th of January, the date in the parameter box has now been physically swapped to actually read as the January date in UK format (11/01/2007) and clicking on the calendar widget confirms that the UI is treating this date as dd/mm/yyyy, but if I now run the report again, it reads the date as though it is in US format, and returns the November data I originally wanted.
So every time I run the report, it seems to flip the format of the date shown in the UI parameter box between US and UK formats, but the SQL code behind the scenes always seems to be reading the value as if it is in US format. So there are 12 days a year that the reports will work fine, 132 days when the reports will provide the correct data second time around, but 221 days a year when they will just crash. :crazy:
If the UI could make up its mind, I could amend the SQL to handle the date appropriately (as long as my users 'think' they are working in dd/mm/yyyy format - I don't really care about what happens behind the scenes), but how can I stop this 'flip-flopping' effect that I am experiencing?
Has anyone else seen this problem, and if so, how did you get around it?
Cheers,
Graham.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

vasile.t SSCommitted Points: 1636 More actions · Answer 1

Graham Smith (11/27/2007)
... I have built a report that uses two dates as parameters within the SQL ...

Could you tell me, what did you select in Report Parameters window? Thanks.

Graham Smith-563576 Valued Member Points: 53 More actions · Answer 2

Sure vasile,

both dates are configured as the data type datetime, and none of the boxes are ticked (hidden, Internal, etc). Available values is set to non-queried but the Label/Value box is empty. Default values is set to Null (I did try setting this to various flavours of Now(), but the box always included the time to the closest thousandth of a second, and all I wanted was the date...)

john.peters Valued Member Points: 66 More actions · Answer 3

One more place to set your language to get the date format correct

Make Sure your browser is set to English Australia as the Language

E.G IE TOOLS\INTERNET OPTIONS \LANGUAGES

Graham Smith-563576 Valued Member Points: 53 More actions · Answer 4

Good point John. I must confess, I hadn't checked that one, but sadly - it was already set to Australian English anyway :Whistling:

Thanks anyway for your input.

G

vasile.t SSCommitted Points: 1636 More actions · Answer 5

You might find a solution in "Microsoft SQL Server 2005 Reporting Services Step by Step" by Stacia Misner.

mlal_fj SSC Rookie Points: 47 More actions · Answer 6

Hi Graham,

I had a similar problem with Reporting Services when creating a report to allow the users to select between two dates.

The problem that I could see was the date that was being displayed on the report was in the US-format. I was almost pulling out my hair by the time I realised that all I had to do was use a format code on the text box as the following:

="dd/MM/yyyy"

I did this through the 'Format' tab on the Properties for the textbox.

I believe you may need to format your date in your query to allow for it to understand that it shoud be looking at the Australian/UK format.

The other thing that you may want to do is use filters on your data set to look at the date parameters rather then writing it up in the query.

This is what I am doing and I have not come across the issue that you are having ie the query understands that date as a US format, as yet.

Kind Regards,

Monita