April 18, 2008 at 6:30 am
Dear All,
I have a simple query with a date that is converted (because I don't want hh:mm:ss).
The query runs prefectly, for me, in SS Management Studio and also from Crystal and Crystal Report Viewier.
However, some users can run the report without any problems and others receive the following error message:
"Crystal Report Viewer
Failed to open a rowset.
Details: ADO Error Code: 0x80040e07
Source: Microsoft OLE DB Provider for SQL Server
Description: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
SQL State: 22007
Native Error: 242
OK"
I have used the ISDATE() function to ensure all dates returned are valid and no problems there.
If I remove the convert from the query it is fine for everybody to use.
I have checked the Language/Country setting and other settings on the users machines who get the error thrown and have checked d/b permissions but cannot find anything wrong/different to mine.
I have also spent ages on the Internet trying to find a solution, but no joy.
Please help me!
Best Regards,
Claire
April 18, 2008 at 6:47 am
Look also at default language for logins these users use.
Piotr
...and your only reply is slàinte mhath
April 18, 2008 at 7:11 am
Thanks for the speedy reply.
I forgot to mention in my post that I have looked at that too.
Any other ideas?
Thanks
April 18, 2008 at 7:26 am
What is the format of date returned from database if you use your account? How do you convert it to requested format?
Piotr
...and your only reply is slàinte mhath
April 18, 2008 at 7:43 am
What is the format of date returned from database if you use your account?
yyyy-mm-dd hh:mm:ss:ms
e.g. 2008-03-04 10:44:54.000
How do you convert it to requested format?
cast(convert(varchar, createddate, 103) as datetime)
returns: 2008-03-04 00:00:00.000
Have also tried: dateadd(dd, 0, datediff(dd, 0, createddate)) which returns same date as cast/convert.
April 18, 2008 at 8:25 am
Try to use this:
convert(datetime, convert(varchar, createddate, 103), 103)
The problem is in the fact that your conversion is using default connection language for outer conversion and if this language happens to have months and days switched, you can get datetime overflows.
HTH
Piotr
...and your only reply is slàinte mhath
April 18, 2008 at 9:18 am
Once again, thanks very much for your help.
Have just picked up your suggestion - shall give it a try on Monday and let you know if it has done the trick.
Best Regards,
Claire
April 22, 2008 at 2:32 am
That worked a treat!
Thank you so much for your help.
Claire
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply