SSRS 2008 R2- Format Parameter to DD/MM/YYYY

0
votes

I have SSRS 2008 R2 and am working on reports for the UK.

My report has a Date/Time data type parameter. When user opens the report the default date format is MM/DD/YYYY, but I would like to have DD/MM/YYYY format (same if user pick date from Calender drop-down date selection).

I have changed the Language settings on the reports to UK and it formats the dates within the dataset, not the parameter. I have also tried using a cdate expression but that does not work either.

One work around is just changing it to nvarchar in my SQL code however, I have a few scripts that use a > @eomdate so the nvarchar will not work in that case.

Any ideas?

3
sharepointreporting-servicesparameterssql-server-2008-r2
Maybe I'm misunderstanding you completely but can't you format the date using FormatDateTime. – maba
So if the user selects a date using the date picker, it is in DD/MM/YYYY format, but the default date is MM/DD/YYYY? How are you setting the default date, is it from a query, an expression or hard coded? – Nathan Griffiths

3 Answers

0
votes

Please see this post on the MSDN forums. The calendar drop down and the date format of the parameter's text box are dependent on the user's Language settings in their browser, not on the report.

If you want to force users to have the same displayed dates, you'll have to use a text field formatted as you want and then use CDate() in your code to turn that varchar into a date for performing time comparisons.

0
votes

This is what I did to resolve the issue in the SQL Code to get it to work in SSRS as a hack.

declare @eomdate varchar(50) set @eomdate = '31/01/2013'

select * from policiesEOM p where p.eomdate >= convert(datetime, @eomdate, 105)

Then I changed the datatype to Text and it works perfectly.

0
votes

The ssrs date type use the browser language in Google Chrome, Mozilla Firefox, Opera and Safari. In Edge too. IE use the windows regional date format.

If you don't want to use date drop box, you can use a text parameter and convert the value to date with ssrs built in convertion function.