Conversion failed when converting datetime from character string

[Tracey]

Hi there
I have moved our ms crm server to a different machine, and installed SQL 2005 to replace the old SQL 2000 on the previous server.

When i migrated my custom reports I began receiving the above error when trying to drill down based on @CRM_FromDate and @CRM_ToDate. I have tried changing the datatype of these params to datetime from string but still receive the error. I believe this problem is based around the US/UK date format differences, however Server Locale is NZ, and dates are being passed in UK format as they should. (dd/mm/yyyy)
I have tried to add convert to the query (where datefield = convert(varchar, @CRM_ToDate, 103) - also tried format 101) but this did not change my errors.

I have been trying to get this to work for a week now and frankly am at my wits end. Can someone PLEASE help me????

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

Its wanting what you have got!

 

[gmmastros]

I urge you to read this thread: thread183-1240616

If you have any further questions, please post them back here.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Tracey]

Hi there thanks for that link, that was VERY interesting info. Of course, I seem to have a tradition here where no matter how long i try to overcome a problem without posting for help anywhere, i am still guaranteed to solve it within 30 minutes of eventually posting. No change today.

My problem was actually solved quite simply in the end. I was passing the parameters from the master report to the drill down by using the value as
=Fields!monthbegindate.Value

to fix the problem this is changed to
=Fields!monthbegindate.Value.ToString()

This then passes the values in US format, as my sql server is expecting.

I will go to the other post to further query your knowledge.....


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

Its wanting what you have got!