Link to home
Start Free TrialLog in
Avatar of dexani
dexani

asked on

Conversion failed when converting date and/or time from character string. (#241)

Hello,

We have the following scenario :

SQL Server 2008 SP3 running on Windows Server 2008 R2.
Custom application running on Access 2007 frontend accessing the database on the SQL backend. Everything in this setup is running 100%

Now we setup the following for a developer:

SQL Server 2008 SP3 running on Windows 7.
The same application running on Access 2007 frontend.
The database is backed up from the producution server and restored to the developers machine.

On the development machine everything runs correctly until you execute any query containing dates, as soon as you do this the following message is displayed :

ODBC-- call failed
[Microsoft][ODBC SQL Server driver][SQL Server] Conversion failed when converting date and/or time from character string . (#241)
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Sounds like a localization issue.
Does Windows Server 2008 R2 run English and the Win7 workstation some other language?

The date fields in question, are they of data type DateTime2? If so, change them to DateTime. The ODBC driver cannot handle DateTime2 other than converting values to string in Access.

/gustav
Avatar of dexani
dexani

ASKER

I agree that it could possibly be a localization issue, but the regional settings and date format is set the same on the "Development server" and the production server, yet the error only occurs when accessing the database on the development server.

I don;t think it is a date field question, since the application works perfectly accessing the production server which contains exactly the same database as the development server.

When change the DSN on the development machine to point to production (with no other changes) the application works perfectly. Change the DSN back to point to the development database and we get the error.
Make sure the SQL SERVER date is "datetime"... not "date".
Scott C
Well, the database is similar but may not be an exact replica.

I found this which could give some inspiration:

http://knowledgebase.datadirect.com/articles/Article/6892

It could also be a client issue it you have picked the wrong client:

http://social.msdn.microsoft.com/Forums/en-US/sqlservermigration/thread/7305ad70-0084-43ec-8379-532d430da9d4

/gustav
In order to avoid these type of problems with SQL Server, you should always use an unambiguous date format such as yyyymmdd
Avatar of dexani

ASKER

I have tried the suggestions above without success, really stuck at this point.
On the development machine everything runs correctly until you execute any query containing dates,
Please post a query that is causing the error to trigger.
Avatar of dexani

ASKER

This is what the query looks like that fails :

SELECT Policy.PolicyNo, Policy.InceptDate
FROM Policy
WHERE (((Policy.InceptDate)=#5/1/2013#));
This is how you do it if you are using T-SQL:
SELECT Policy.PolicyNo, Policy.InceptDate
FROM Policy
WHERE Policy.InceptDate = '20130501'

Open in new window

Avatar of dexani

ASKER

You are correct that the native SQL query works in SQL itself, but the Access query gives the error when executed from within Access...
Which SQL driver are you using when attaching tables from SQL Server?

/gustav
Avatar of dexani

ASKER

I have tried SQL Server native client 10, 11 and the version before that...
That may be the issue. Try that named "SQL Server" only.

/gustav
Avatar of dexani

ASKER

Did try "SQL Server" only, no success...
Then I'm out of ideas, sorry.

/gustav
ASKER CERTIFIED SOLUTION
Avatar of dexani
dexani

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>  you must have the same version of the type library on your development computer and
> on other computers that will be using the database. Or you must refresh the
> reference to the type library on the computers that will be using the database

Not to play the clever guy, but you didn't mention that a library is in use. Also, it is a well-known requirement that the database application must be compiled with any libraries in use. If you later change/update a library, the application must be compiled again.

But thanks for the feedback. It explains your situation.

/gustav
Avatar of dexani

ASKER

The question was resolved after researching the error on the MS support site, as per the KB article below :

http://support.microsoft.com/kb/194374