Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 2,114, Visits: 3,720
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 2,114, Visits: 3,720
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 2,114, Visits: 3,720
Thanks, does select * from mysql_apply...apply_2 return the data sucessfully?
Post #1419106
Posted Tuesday, February 12, 2013 10:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 2,114, Visits: 3,720
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 2,114, Visits: 3,720
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»»

Permissions Expand / Collapse