Date Issue (SQL Server Express Reporting Services)

[Strider]

Hey there...

Im pretty new to using the SQL Server Reporting services. Im using it in SQL Express for the first time. And i have been playing around with it.

However as always with Microsoft there are date issues.

Ideally i would like all my date formats to be of "DD/MM/YYYY" since my location is ireland. Now i have been using the FormatDateTime(SomeExpression, 2) to obtain the date parts for various items in my reports. For Example, when giving a default to a parameter box this will show a date in the correct way 09/05/2005.

My main issue is when i do a query, i need to change the parameter to be of US dateformat ("MM/DD/YYYY") or else internation ("YYYY/MM/DD") in order for my query to work.

What is the best way to get around this?

Its very frustrating

[ganeshmoorthy]

Is it neccessary to query the database with format function...or you can just try like Year (Date), Month(Date) and Day(Date), I hope these are available in SQL Express edition...

[Strider]

I don't query the database with the Format Function, i only use the format in the report fields to convert the date value returned from the query to being a short date

If i want to get all rows from May 1st to May 10th
i would like to use 01/05/2007 and 10/05/2007 in the parameters of the query/report

however this does not work. It presumes these dates are 5th January and 5th October.

Even when i run my queries it will return the dates in US format. I dont know why.

[Strider]

Looks like its caused by the fact that Reporting Services is always installed as English US.

[ganeshmoorthy]

check the system date settings and also is it possible to change the query definition...like instead of passing the date as it is, pass the date in three values i.e. day, month, year for example GetDate(Day As Integer, Month As Integer, Year As Integer), so that you dont need to change the format of the date for querying...and it is easy to format for the report by just clubbing day + '/' + month + '/' + year