how to convert a MySQL date (linked server) to MS SQL?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » SQL Server 2008 - General » how to convert a MySQL date (linked server)...

Add to briefcase

12 posts, Page 1 of 2

1 2 »»

how to convert a MySQL date (linked server) to MS SQL?

Expand / Collapse

Author

Message

Posted Tuesday, February 12, 2013 10:01 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 5:53 AM
Points: 5, Visits: 11

Hi, I'm stuck on converting a MySQL date field (on a linked server) to MS SQL datetime format. I've googled this, but am having trouble finding something useful (other than using OpenQuery, but that's not ideal for a large update command with joins between the MySQL and MS SQL tables).

Here's an abbreviated version of my update command:

UPDATE dbo.NCU_Apply_FromWeb
SET guardian1_attended_date = A_D.guardian1_attended_date
from NCU_Apply_FromWeb NAW
inner join mysql_apply...apply_2 A_D on A_D.uid = NAW.uid

Error message:
Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

Any suggestions?

Post #1419066

Posted Tuesday, February 12, 2013 10:08 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:53 AM
Points: 1,474, Visits: 2,342

At a guess, you're encountering out-of-range values from MySQL (probably lovely old '0000-00-00 00:00:00') - if you can convert these to null it should work, I think. Last time I did this it was using OPENQUERY so not sure if it'll work in a straight join.

Cheers
Gaz

Post #1419073

Posted Tuesday, February 12, 2013 10:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 5:53 AM
Points: 5, Visits: 11

Thanks, Gaz, but that's not it.

The MySQL values are:
2013-02-03 00:00:00.000
2013-02-11 00:00:00.000

Post #1419075

Posted Tuesday, February 12, 2013 10:36 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:53 AM
Points: 1,474, Visits: 2,342

Hi,
Are those values for the entire table, not just the matching rows? My feeling is SQL will grab all the data before trying the join.

Thanks

Post #1419098

Posted Tuesday, February 12, 2013 10:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 5:53 AM
Points: 5, Visits: 11

Those are the values for A_D.guardian1_attended_date (from the query above). Yes, there are only 2 rows that are joined.

Post #1419100

Posted Tuesday, February 12, 2013 10:48 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:53 AM
Points: 1,474, Visits: 2,342

Thanks, does select * from mysql_apply...apply_2 return the data sucessfully?

Post #1419106

Posted Tuesday, February 12, 2013 10:50 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 5:53 AM
Points: 5, Visits: 11

No. I neglected to mention I use this before the select:
DBCC TRACEON(8765)

However, I still get
Msg 8114, Level 16, State 8, Line 2
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

when I run
DBCC TRACEON(8765)
select * from mysql_apply...apply_2

Post #1419109

Posted Tuesday, February 12, 2013 11:01 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:53 AM
Points: 1,474, Visits: 2,342

No worries, not a trace flag I've used before but not sure it'll help with date fields.
Out of range datetimes was about all I had for this - hopefully someone else will be along with another suggestion.
Cheers
Gaz

Post #1419118

Posted Tuesday, February 12, 2013 11:04 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 5:53 AM
Points: 5, Visits: 11

Thanks anyways, Gaz!

Post #1419120

Posted Wednesday, February 13, 2013 3:43 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, May 20, 2013 1:53 AM
Points: 1,474, Visits: 2,342

No worries. Just noticed these are your first posts on the site - welcome to SSC!

Post #1419375

« Prev Topic | Next Topic »

Add to briefcase

12 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse