MySQL => MSSQL; Converting date to text


I'm trying to import data from a MySQL tabel with the following statement


In plain text: I want to import dates (fieldtype: date) form MYSQL AS TEXT into a MSSQL field.
It doesn't work and i get a big errormessage:

[Microsoft][ODBC SQL Server DRIVER][SQL server]OLE DB error trace [OLE/DB Provider 'MSDASQL'IColumnsInfo::GetColumnsInfo returned 0x80004005: The provider did not give any informatie about the error.]

May be it has something to do with an older MySQL driver version?

I won't you bother with the reasons why, but how do I convert dates into text form MySQL into MSSQL?

Gr, Walter

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

First, I would suggest not using CONVERT, its not SQL-92 compliant.  If you have to change the type, then try CAST.  Otherwise, try bringing it in as a date and then changing it once its in MSSQL.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
in MySQL, convert can used to translate character sets:

as PaulBarbin indicates, you can use CAST, but CONVERT should also work:


Walter4Author Commented:
Hi AngelIII,

I'm do it all with a Stored Procedure looking like this (following youre advice):


Insert into T_dbc001_dbc_registraties.dbc_begindat
Select dbc_begindat    
FROM         OPENQUERY(MYSQL_DBC, 'SELECT CONVERT(dbc_begindat,char) FROM t_dbc')

BUT again i get an error message:

Microsoft SQL-DMO (ODBC SQLState: 42000)
Error 7399: OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005:  The provider did not give any information about the error.].

So what's wrong?

Gr Walter
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
What is the OUTPUT of this query (if it works)?
Select dbc_begindat    
FROM         OPENQUERY(MYSQL_DBC, 'SELECT dbc_begindat FROM t_dbc')

Walter4Author Commented:
The advice of PaulBarbin seems to be fine, but I do want to import dates before 1753 (see my earlier questions).
So the only way is to import them as text in MSSQL and then see what I can do with these old dates.

I do need those old dates because I want to confront some employees with wrong input in the datefields of the MySQLdatabase

Gr, Walter
Have you tried it without the convert to narrow the problem down?  It might be just a driver problem like you said.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Walter4Author Commented:
All right I'll try to describe it in more detail.

1) We have a MySQL-database called: MySQL_DBC
with a table called:                                t_dbc.
Besides text and numeric fields there are also date fields in this table.

2) The connection to this MySQL-database is as a linked server in MSSQL.

3) To get the data from the MySQL table in a MSSQL-database we make a view in the specific MSSQL database with the following statement:

In the view result I see indeed all the records of the MySQL-table so far so good.

4) The next step is to get the viewresults in a MSSQL table and there it goos wrong!
Because there are some dates in the viewresult before the year 1753 MSSQL sends errormessages.

5) To overcome this I do want to get the MySQL dates as text in the viewresult so MSSQL doesn't have problems with the wrong dates. in trying this I used the CONVERT or CAST functions in de above statement, but as a result i get  the errors described in my last comment.

Is it clear?

Gr Walter

Have you tried the insert without the convert, it might be able to make the conversion implicitly.
Walter4Author Commented:
Hi PaulBarbin,

Yes I've tried that, but then I am confronted with the problems: dates before the year 1753, which I want to import in the MSSQL table.
No, I mean setup the sql table as a varchar and then try the implicit conversion between the outputted MySQL date field and the varchar field in SQL


p.s. sorry been in meetings all day
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.