Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Aug 2006
    Location
    Cardiff, UK
    Posts
    141
    Thanks
    15
    Thanked 2 Times in 2 Posts

    Converting dates between MSSQL and MySQL

    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?
    If anyone asks my boss, this counts as work, okay?

  • #2
    Regular Coder
    Join Date
    Aug 2006
    Location
    Cardiff, UK
    Posts
    141
    Thanks
    15
    Thanked 2 Times in 2 Posts

    Solved!

    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.
    If anyone asks my boss, this counts as work, okay?

  • #3
    Senior Coder tomws's Avatar
    Join Date
    Nov 2007
    Location
    Arkansas
    Posts
    2,644
    Thanks
    29
    Thanked 330 Times in 326 Posts
    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
    Are you a Help Vampire?

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Those dates don't look like UNIX TIMESTAMP to me.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,458
    Thanks
    76
    Thanked 4,373 Times in 4,338 Posts
    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.
    Last edited by Old Pedant; 06-25-2009 at 06:23 AM.

  • Users who have thanked Old Pedant for this post:

    tomws (06-25-2009)

  • #6
    New to the CF scene
    Join Date
    Aug 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Migrate almost any data

    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

  • #7
    New to the CF scene
    Join Date
    Aug 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  • #8
    New to the CF scene
    Join Date
    Jan 2010
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by wonn1377 View Post
    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


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •