MSSQL datetime snafu

  • I have an mssql database that I need to migrate to mysql. For some reason though any queries to any columns of type datetime are returned in French in the following format:

    7/avr/2004 14:00

    This also includes all the text of an SQL dump which not surprisingly makes mysql unable to read the dump from the MSSQL db. I've checked that my windows regional setting is set to English. I'm at a loss as to why this is happening or how to fix it. I just need them to be in mdy h:m:s format. One interesting thing I've noticed also is that if I look at the tables via the MSSQL Server Manager the dates are displayed as mdy h:m:s. Also I've tried doing

    SP_DEFAULTLANGUAGE @loginame = username, @LANGUAGE = english

    and even after this I still get the following results:

    -If I connect via PHP and then query the database I get the French dates.

    -When I run the sqlDump program it connects to the database and all the dates in the dump are in the French format.

    -If I run SQL Server Manager and do a query with its query editor I get back the dates in the expected 2004-03-10 10:45:00 format.

    Thanks for any help, I'm totally stumped! -Adam

  • What's the language setting for the server? Not the database, the server. (Properties on the connection in Management Studio.)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • That's listed as English also. Both the connection and the server have English listed as their language.

  • Although not sure about it but default collation for mysql is not English its Swedish. Please check collation of all tools involved (MSSQL, sqlDump and MySQL).

    For workaround, you can convert datetime in some other datatype like char or number and then try to migrate it, after migration change it back to datetime in MySQL. You may also use one extra column for this and drop it once you are done.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply