Date format passing parameter

[Tracey]

Hi there
I am trying to write customized reports for MSCRM 3.0 using reporting services.

I have existing reports written by someone else that was running fine on the old CRM server, but now when i try to drill down for data i get an error message Conversion failed when converting datetime from character string"

I have pretty much tracked this down to the fact that the drill-down report's DS is wanting the dates in us format but they are being passed in uk format (which is what we use) how can i change the format of the date parameter which is being passed to the drill-down report?

Tracey
Remember... True happiness is not getting what you want...

Its wanting what you have got!

 

[unclerico]

In the query/stored proc for the drilldown report, you'll want to convert the date format:

SELECT field1, field2, field3
  FROM table1
 WHERE date_field = CONVERT(varchar,@inputvariable,101)

check BOL for keyword CONVERT, it will have all of the different date formats that you can use and the code (the third parameter in the CONVERT function) needed to perform the conversion.

 

[Tracey]

Hi there
thanks for your answer, but that doesnt help regardless of whether i format to us(101) or uk(103) i still get date out-of-range errors

Frankly this whole CRM/RS thing is so flakey any remaining respect i had for M$ has withered and died along with my sanity.




Tracey
Remember... True happiness is not getting what you want...

Its wanting what you have got!