?
Solved

Convert MySQL date to MSSQL datetime and if possible smalldatetime

Posted on 2007-10-12
26
Medium Priority
?
2,453 Views
Last Modified: 2012-06-27
I have a table in a MySQL database that I want to convert to the new MSSQL type of database so that I can use visual studio to devel an application to work with the data.

I created a linked server that uses the MySQL ODBC 3.51 to access the original MySQL database on the remote server. I then use this on the MSSQL side:
SELECT * INTO TEST.dbo.thefiles
FROM openquery(MySQL, 'select LoadID, FileID, MiscTxt, ... from thefiles')

I am able to import most of the columns but I am having trouble with a column called CheckDate.
The null or default value for this field is '0000-00-00' in the MySQL database so I did a cast to prevent MSSQL from complaining about 0000 not being a date at all:
cast( case when CheckDate LIKE ''0000%'' THEN NULL ELSE CheckDate END as date) as CheckDate

When I run the query:
SELECT * INTO TEST.dbo.thefiles
FROM openquery(MySQL, 'select LoadID, cast( case when CheckDate LIKE ''0000%'' THEN NULL ELSE CheckDate END as date) as CheckDate from thefiles')
It fails with error:
OLE DB provider "MSDASQL" for linked server "MySQL" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].CheckDate" from OLE DB provider "MSDASQL" for linked server "MySQL". Conversion failed because the data value overflowed the data type used by the provider.

I don't understand why this is happening because I can do the sql statement replacing the CheckDate with another column that has the exact same Datatype as CheckDate called MUDate and it works fine.
What am I missing here???

Also is there a way to get the resultant datatype on MSSQL to be smalldatetime?
Thank You
0
Comment
Question by:jpwallen
  • 13
  • 7
  • 4
  • +1
26 Comments
 
LVL 5

Expert Comment

by:Crag
ID: 20064935
I notice that your MySQL column CheckData has the same name as the MSSQL column. Have you tried this after changing the name of the MSSQL column?
Also do you really need the as date part of the cast statement?
0
 
LVL 1

Author Comment

by:jpwallen
ID: 20065018
Yes, I have tried changing the name of the MSSQL column by:
...CheckDate END as date) as SomethingElse from thefiles')
But since this statement actually creates the tables I cannot just "Change" the name of the collumn;
I have to do it in the sql query.


If I don't put the as date it formats the column as text which doesn't help me at all.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20065419
please try this:

SELECT loadid, convert(datetime, checkdate, 120) as checkdate
INTO TEST.dbo.thefiles
FROM openquery(MySQL, 'select LoadID, case when CheckDate LIKE ''0000%'' THEN NULL ELSE CheckDate END as CheckDate from thefiles') l
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:jpwallen
ID: 20065483
In response to angelIII:::
It says:
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20065518
my wrong:



SELECT loadid, convert(datetime, checkdate, 120) as checkdate
INTO TEST.dbo.thefiles
FROM openquery(MySQL, 'select LoadID, case when CheckDate LIKE ''0000%'' THEN NULL ELSE DATE_FORMAT(CheckDate, ''%Y-%m-%d'') END as CheckDate from thefiles') l
0
 
LVL 1

Author Comment

by:jpwallen
ID: 20065563
It says:
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 20065597
You don't need to switch to MS SQL Server to work with dotNet:

http://dev.mysql.com/tech-resources/articles/dotnet/

Exploring MySQL in the Microsoft .NET Environment.

/gustav
0
 
LVL 1

Author Comment

by:jpwallen
ID: 20065740
I dont want to use that database for my new application.
I just really want to get data from it into another database.
I don't really need the full tables but I do need that CheckDate column.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 20065848
Then create a pass through query where you may use the MySQL syntax, like:

 .. CAST(CheckDate As DateTime)

or format CheckDate as a UCT date/time string.

/gustav
0
 
LVL 1

Author Comment

by:jpwallen
ID: 20065998
The problem still exists that I cannot convert the CheckDate to DateTime
0
 
LVL 5

Expert Comment

by:Crag
ID: 20066045
I see where I went wrong before, try this:

SELECT * INTO TEST.dbo.thefiles
FROM openquery(MySQL, 'select LoadID, cast( case when CheckDate LIKE ''0000%'' THEN NULL ELSE CheckDate END as datetime) as CheckDate from thefiles')

otherwise seperate the import from the conversion, try this:

SELECT * INTO #Import
FROM openquery(MySQL, 'select LoadID, CheckDate from thefiles')

update #Import
set CheckDate = null
where CheckDate = LIKE ''0000%''

SELECT * INTO TEST.dbo.thefiles
FROM #Import
0
 
LVL 1

Author Comment

by:jpwallen
ID: 20066118
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'LIKE'.

Remember LIKE is a MySQL term and not MSSQL.
So MSSQL has no Idea what that means.
0
 
LVL 5

Expert Comment

by:Crag
ID: 20066336
Apologies
LIKE is also a term in SQL Server - I've used it in loads of queries with the expected results.
I just formatted it incorrectly.

revised query

SELECT * INTO #Import
FROM openquery(MySQL, 'select LoadID, CheckDate from thefiles');
go

update #Import
set CheckDate = null
where CheckDate = LIKE '0000%';
go


SELECT * INTO TEST.dbo.thefiles
FROM #Import;
go
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20066358
>Incorrect syntax near the keyword 'LIKE'.
that is because of the = LIKE , it should be only LIKE (remove the = before like).

>Remember LIKE is a MySQL term and not MSSQL.
MS SQL also knows LIKE syntax, actually...

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20066376
what does this return:

SELECT loadid, FormattedCheckDate
FROM openquery(MySQL, 'select LoadID, DATE_FORMAT(CheckDate, ''%Y-%m-%d'') FormattedCheckDate from thefiles')

0
 
LVL 1

Author Comment

by:jpwallen
ID: 20066412
In response to angelIII:::
I returns no error but the FormattedCheckDate looks like it is in text format
0
 
LVL 5

Expert Comment

by:Crag
ID: 20066425
Thanks angeliii - never spotted the =

SELECT * INTO #Import
FROM openquery(MySQL, 'select LoadID, CheckDate from thefiles');
go

update #Import
set CheckDate = null
where CheckDate LIKE '0000%';
go


SELECT * INTO TEST.dbo.thefiles
FROM #Import;
go
0
 
LVL 1

Author Comment

by:jpwallen
ID: 20066464
Msg 7342, Level 16, State 1, Line 1
An unexpected NULL value was returned for column "[MSDASQL].CheckDate" from OLE DB provider "MSDASQL" for linked server "MySQL". This column cannot be NULL.
Msg 208, Level 16, State 0, Line 2
Invalid object name '#Import'.
Msg 208, Level 16, State 0, Line 3
Invalid object name '#Import'.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20066477
>I returns no error but the FormattedCheckDate looks like it is in text format
that is fine... the text format is good, actually.
now, do you "see" any values in there that are not valid dates, apart from the 0000-00-00 values?

please try this:

SELECT loadid, case when FormattedCheckDate like '0000-%' then null else convert(datetime, FormattedCheckDate , 120) end as CheckDate
FROM openquery(MySQL, 'select LoadID, DATE_FORMAT(CheckDate, ''%Y-%m-%d'') FormattedCheckDate from thefiles')

if that still returns the same error, you have dates that are before year 1753 or after 9999.
if that is the case, you will need to take a decision what to do with those...
0
 
LVL 1

Author Comment

by:jpwallen
ID: 20066553
Hmm, Yes it starts to work then says:
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Thats interesting because I do a:
SELECT * FROM thefiles ORDER BY CheckDate DESC
On the MySQL server itself
The top value is 2020-07-12
Lowest non null value is 2001-05-04

Know any way of finding that out-of-range value?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20066976
run this, please, and see the rows returned:

SELECT loadid, FormattedCheckDate
FROM openquery(MySQL, 'select LoadID, DATE_FORMAT(CheckDate, ''%Y-%m-%d'') FormattedCheckDate from thefiles') l
where isdate(FormattedCheckDate) = 0

should give you an idea of what values are wrong
0
 
LVL 1

Author Comment

by:jpwallen
ID: 20067589
Well, This seems to return every value that is the default null value ( the 0000-00-00 )
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20069202
ok, let's eliminate those ones:

SELECT loadid, FormattedCheckDate
FROM openquery(MySQL, 'select LoadID, DATE_FORMAT(CheckDate, ''%Y-%m-%d'') FormattedCheckDate from thefiles') l
where isdate(FormattedCheckDate) = 0
   and FormattedCheckDate not like '0000%'
0
 
LVL 1

Author Comment

by:jpwallen
ID: 20081025
Yes, thank you very much. The offending values were:

2006-00-12 (The 0'th month) and
2007-02-29 (There is no Feb 29)

This was driving me crazy and I may have never spotted it without your help.

My last question is, if possible, is there any way to have it automatically format the FormattedCheckDate column as a smalldatetime? Since we are not dealing with values before the 20'th century I think it would work a little faster if I create this column as a smalldatetime.
0
 
LVL 1

Author Comment

by:jpwallen
ID: 20084794
If not, thats ok to...
0
 
LVL 1

Author Comment

by:jpwallen
ID: 20084844
I used:
SELECT LoadID, convert(datetime, CheckDate, 120) as CheckDate...
to get the datatypes I needed
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question