MySQL => MSSQL; Converting date to text

Hi,

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

INSERT INTO [TABEL1].DATE
SELECT DATE
FROM         OPENQUERY(MYSQL_DATABASE, 'SELECT CONVERT(NVARCHAR,DATE) FROM mysql_tabel')

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



Walter4Asked:
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.

PaulBarbinCommented:
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.

Paul
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in MySQL, convert can used to translate character sets:
http://www.mysql.com/doc/en/Charset-CONVERT.html

as PaulBarbin indicates, you can use CAST, but CONVERT should also work:
http://www.mysql.com/doc/en/Cast_Functions.html

INSERT INTO [TABEL1].[DATE]
SELECT DATE
FROM         OPENQUERY(MYSQL_DATABASE, 'SELECT CONVERT(DATEFIELD, CHAR) FROM mysql_table')

CHeers
Walter4Author Commented:
Hi AngelIII,

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

CREATE PROCEDURE dbo.test_1 AS

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.].
---------------------------
OK  
---------------------------


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')

CHeers
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
PaulBarbinCommented:
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:
SELECT *
FROM         OPENQUERY(MYSQL_DBC, 'SELECT * FROM t_dbc')

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





PaulBarbinCommented:
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.
PaulBarbinCommented:
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

Paul

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.