Feedback
Did this article resolve your question/issue?

   

Article

Error converting data type DBTYPE_DBDATE to datetime querying a date field using Microsoft SQL Server

« Go Back

Information

 
TitleError converting data type DBTYPE_DBDATE to datetime querying a date field using Microsoft SQL Server
URL Name000044844
Article Number000165523
EnvironmentProduct: OpenEdge
Version: All supported versions
OS: Windows
Other: Microsoft SQL Server
Question/Problem Description
Error converting data type DBTYPE_DBDATE to datetime querying a date field using Microsoft SQL Server.

Error converting data type DBTYPE_DBDATE to datetime occurs when querying a date field using a Microsoft SQL Server Linked Server.

Querying an OpenEdge database table that contains a field of type DATE from a Microsoft SQL Server Linked Server.
Steps to Reproduce
Clarifying Information
Executing a query similar to the following using the Microsoft SQL Server:

SELECT * FROM OPENQUERY(linked_server_name, 'SELECT SomeDate FROM table1')
 
Querying other DATE fields does not cause the error.

Issue is not reproducible when the same query is executed using WinSQL or Microsoft Excel.

Issue did not occur previously when the same query was executed by the same Microsoft SQL Server.
Error MessageSQL error; msg 8114, Level 16, State 10, Line 1
Error converting data type DBTYPE_DBDATE to datetime.
Defect/Enhancement Number
Cause
Recent DATE field data corruption due to user input, application logic or some other factor. Invalid DATE column values like "1/0/1900" and "0249-12-24" were detected in the offending table.  
Resolution
Step #1
Review current data in the DATE columns of the table and correct any records containing invalid DATE values.

Step #2
Identify the parts of the application that update the affected tables.
Add validation to these parts to ensure that only valid date values are submitted to the database.
 
Workaround
Use a more tolerant ODBC client, like WinSQL, to query the offending column.
Notes
References to other documentation:

Progress article(s):
000011294, "How to query a Progress database via a Microsoft SQL Server Linked Server?"
Last Modified Date3/22/2019 8:28 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.