Dateformat(SQL Server2005 ) not working in Report Studio of Cognos8

[phanicrn]

Hi

We have Cognos8, MS Sql Server 2005.
In database calendar Date reads as 2008-01-26 12:00:00 AM, but in FM  calendar_Date(Columns)shows data as january 31 2008 12:00:00 AM. I am specifying a format as  mm/dd/yyyy in queritem properties. now in report studio. when i bring calendar date in list and execute report, it shows data in mm/dd/yyyy format, but when i try to filter for specific date to test report. it doenst filter and no errors when validate. report executes with no data.

one more thing i have tested..
After calendardate column is imported to FM and i have created calculation  to convert(Varchar(10),Calendar_Date,121) gives me data as 02-26-2008, now when i filter calendardate(calculated column ) it works.

is there something i have to do..?
phani

[blom0344]

You have to distinguish between the representation of dates and the format that is stored in the database. If you use a filter this is will be added to the sql as such , expecting the format that resides in the database. Now, if you use a prompt you can choose to set a certain format and this will be presented as you run the report..