Converting dates between MSSQL and MySQL

butlins · 06-24-2009, 11:50 AM

I've just had my first project which involves migrating a MSSQL database into MySQL, and I've just hit my first puzzler (which I'm hoping is something that anyone who performs these migrations regularly will find trivial).

The dates seem to be stored as the equivalent of a UNIX timestamp - values like 38376.51454 and 39406.75522 - and I've searched both Google and CodingForums.com, but to be honest I'm not sure of the terminology, so I'm not sure if I'm searching for the right thing.

I have MSSQL Server Management Studio 2005, am running MySQL 5.0.41 and have the MySQL GUI Tools (although I've not persuaded the Migration Assistant to work) and Navicat, in case there's anything that I can do when actually migrating the data, rather than transforming it after.

Ideally, I'd like the dates stored as MySQL dates, as that would make it easier for me to continue working with the imported data, and create new entries.

Can anyone point me in the right direction?

butlins · 06-24-2009, 12:22 PM

I should have persisted a little longer with my search! If anyone else is looking for the answer on how to convert between MSSQL Datetime and Unix Timestamp formats, I found a handy guide here.

tomws · 06-24-2009, 02:06 PM

Not sure if you've tried it out yet, but MySQL has a migration toolkit available for download. Handles data migration automatically if you're just moving data from one db to the other. I've used it for Access-to-MySQL migration.

http://dev.mysql.com/downloads/gui-tools/5.0.html

guelphdad · 06-24-2009, 02:13 PM

Those dates don't look like UNIX TIMESTAMP to me.

Old Pedant · 06-25-2009, 06:21 AM

They aren't Unix timestamps: They are actually ODBC DATETIME values, not native SQL Server values. I'm guessing he is getting the data from SQL Server via an ODBC connection of some sort.

They are the number of days since 30 December 1899 expressed as a floating point number. That is, the integer part is the number of days; the fraction is the fraction of 24 hours on the date in question. (e.g., 17.75 would be 17 January 1900 18:00:00)

Those two dates are:
1/24/2005 12:20:56 PM
11/20/2007 6:07:31 PM

(Easy to do in MSIE using VBS code

Code:

<html><body>
<script language="VBScript">
document.write CDATE(39406.75522 ) & "<br>" & CDATE(38376.51454)
</script>
</body></html>

SQL Server uses an entirely different scheme for encoding datetime values. Two integers: First is number of days since 1/1/1900 and second is number of funny units since midnight. If I remember correctly, the funny units are 1/3 of a millisecond, or something like that. It's all in the SQL Server docs, of course.

wonn1377 · 08-21-2009, 01:03 PM

I think u should use a third party help to resolve this problem, i use dbload to solve it when i was migrated my data, it can migrate almost any data, it helps me to convert MSSQL to MYSQL, MS access to MSSQL, mysql, csv loader, foxpro and MSSQL to MS access, MYSQl, CSV, foxpro etc. i found it on google search.

Download Free : http://www.dbload.com

jean3167 · 08-31-2009, 10:59 AM

Here is a tool to convert MSSQL to MYSQL database. That i found on google search it this converter can do migrate any data try and tel me is that worth or not.

shumaker212 · 03-03-2010, 01:40 PM

Quote:

Originally Posted by wonn1377

I think u should use a third party help to resolve this problem, i use dbload to solve it when i was migrated my data, it can migrate almost any data, it helps me to convert MSSQL to MYSQL, MS access to MSSQL, mysql, csv loader, foxpro and MSSQL to MS access, MYSQl, CSV, foxpro etc. i found it on google search.

Download Free : http://www.dbload.com

Is this work on linux