December 20, 2011 at 11:29 am
Hi,
I have a crystal report when opened in the server gives the result properly. My DB server has 2 databases. In the database server the report opens fine for both db.
When tried from another machine gives "It failed in conversion while converting it from the character string into the datetime type" error. This error comes for only one database. If I point the report to another db of same DB server it works fine.
Can anyone help please ?
December 20, 2011 at 11:55 am
Check the datatypes in the tables used by that report. One of them is different than the rest of the databases you are accessing.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 20, 2011 at 12:01 pm
Hi,
The report is configured in DB server. I access through VB application. It works fine for both DBs when tried from DB server. When I run from another machine having shortcut to this VB appln it fails.
So I tried opening the crystal report and passed the inputs. That time it throws the ODBC error saying datetime conversion fails. 🙁
December 20, 2011 at 12:39 pm
This sounds like it might be due to differences in the language settings between the various computers. To prevent these kinds of problems, you want to make sure
* All date/time data is stored in datetime fields
* All date/time parameters are defined as datetime
(Crystal sets new parameters to strings as defaults.)
* Any hard-coded dates should use a neutral format such as 'YYYY-MM-DD'
(I hope that the only hard-coded date is 1900-01-01 or some similar reference date.)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 20, 2011 at 1:10 pm
The date is stored as datetime only. there is no string or varchar used for date in the SP. The rpt file use the same SP in both databases. for one db it works and for other it fails. The SP version is also same. I compared it.
December 20, 2011 at 1:54 pm
What are the language settings as Drew mentioned?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 22, 2011 at 12:20 am
In ODBC the SQL language is English. The Regional settings is Japanese. This is settings in workstation. In the database server the regional settings is English
December 22, 2011 at 6:59 am
senthil_sn1 (12/22/2011)
In ODBC the SQL language is English. The Regional settings is Japanese. This is settings in workstation. In the database server the regional settings is English
This goes back to my original statement. Somewhere you have a date stored as a string. Japanese and English (U.S.) have different settings for date formats. When you convert your string to a date, it matches the expected format for one, but not the other. You just need to figure out where you have a date stored as a string and either change it to a date or use a universal format such as YYYY-MM-DD.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply