Problems with date formats

The date fromat in my country (Australia) is DMY.

It seems to be quite difficult to prevent the month and day being swapped around. I had a case recently where we generating a list of dates to be presented in the report server UI (User Interface) as a list box for a parameter selection.

We fixed the problem in the data set (on the data tab) by using "SET DateFormat DMY" only to find that the month and day were still swapped around in the UI list box. If this was just a presentation issue we could live with it but it turned out that the value read into the parameter was incorrect (the day and month were actually swapped).

To fix the problem, we had to chnage the data type to VarChar() in the data set query, using Convert() to force an unambiguous format (113).

As far as I know we have set the correct locale and language in all the right places. Does anyone have any suggestions on how to beat the date format woes?

Dick Campbell

The locale used to prompt for date formats is based on the browser language that is passed to the server. Is IE set for "English (Australia) [en-AU]"?

|||

Thanks, that may well fix the problem. I won't be able to test it until I get to work tomorrow.

I am convinced that the UI is causing the problem so IE could well be the culprit.

It's a pity that the "lanuage" and "locale" have to be set in so many places, Windows, SQL Server and now IE.

Thanks again for the help.

Dick Campbell

|||

A final comment, It seems that there are there levels at which the date format can be set, Windwos, SQL Server and Internet Explorer. When using the Reports Server UI, all three can come into play and have the potential to reverse the day and month.

Our work-around for now is to always specify date/time parameters to be a string data type and have a defualt value that indicates an unambiguous format for example Now().ToString("dd-MMM-yy HH:MM").

Another reason for doing this is the the Calendar Control in the UI does not have time information so that the time has to be typed in after the date has been selected, triggering yet another page refresh.

|||There is an issue with SSRS dateformats in that it converts them to US Format no matter what you do by default.
I am in the UK and wanted the dates in ddmmyyyy format and the solution to this is to apply SQL2005 Service pack 1 to the server (And all your client running management studio etc) this then gives you a british format date.
You can use formatdatetime(MYDATE, 1/2/3/4/5) then to get the details you need