SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    MS SQL dates -> MySQL Dates Automatically?

    We're using data feeds that were originally meant for MS SQL and the dates in the data feed (tab delimited text files) are formated like:

    Oct 21 2007
    In MySQL, the date fields are formatted as datetime fields and when we do an import the dates all come through as 0000-00-00 00:00:00.

    We're importing using the MySQL "LOAD DATA LOCAL INFILE" command and just dumping the text files right into freshly truncated tables.

    Is there a way when loading the files to find and replace the dates maybe? Can MySQL convert the dates?

    I have a shell script downloading an archive and uncompressing, then it runs a PHP script that loops through the files runningt he LOAD DATA command.

    Any advice would be appreciated... I'm just looking for the easiest (and least server-intensive) way to get the date issue fixed. We've already tried begging the vendor but they're not yet ready to start supporting other date formats (even though their MS SQL db can output a date format friendly to MySQL).

    Thanks!

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    when using load data use a variable to indicate the column in the file that you want to change and then use SET to set how you want that column to be and run DATE_FORMAT on it:

    Code:
    LOAD DATA INFILE 'blah\blah\blah'
    INTO TABLE tablename
    FIELDS TERMINATED BY 'whatever'
    LINES TERMINATED BY 'endofline'
    (@junkdate, column2, columnthree, col4four),
    SET datecolumn = DATE_FORMAT(@junkdate, '%Y-%m-%d')
    NOTE: untested

  3. #3
    SitePoint Wizard silver trophy
    beley's Avatar
    Join Date
    May 2001
    Location
    LaGrange, Georgia
    Posts
    6,117
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I didn't know you could do that with the LOAD DATA command.

    I wonder if it could be done just as a search and replace int he shell script? The reason I'm hesitant to use the method above is there are about 8 files (8 tables), and they all have different fields (and a LOT of them) so it would likely take a pretty long time to go through and make a LOAD DATA command for each of them. Right now it's just generic and has a PHP variable echo the name of the table and file.


Bookmarks

Posting Permissions

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