Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Posts
    4

    Unanswered: mysql query problem

    Hi guys,

    I need to convert a date field(dd/mm/yyyy) into mysql date column(yyyy-mm-dd)

    table
    ========

    CREATE TABLE fujitsu_spare_import (
    date_temp date NOT NULL default '0000-00-00',
    time time NOT NULL default '00:00:00',
    scan_type text NOT NULL,
    fujitsu_order_number text NOT NULL,
    technician_or_shippiment text NOT NULL,
    import_status tinyint(1) default '0'
    ) TYPE=InnoDB;



    query
    ========

    LOAD DATA INFILE "P:\\test.csv"
    INTO TABLE fujitsu_spare_import
    FIELDS TERMINATED BY ','
    (str_to_date(date_temp, '%d/%m/%Y'),time,scan_type,fujitsu_order_number);

    Example csv file
    ======

    21/06/1921,15:20:45,R,9000804991Test,,0



    Database - mysql 5.0
    ===============

    The above query is not working, can anyone pls help me out.

    Cheers
    Anand
    Last edited by anandsatchin; 10-23-06 at 11:44.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    add another column to the table, VARCHAR(10), load the date string into this extra column, then run an UPDATE statement with STR_TO_DATE, and finally ALTER the table and drop the extra column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2006
    Posts
    4
    Thanks ,very muh for the help...

    I will use ur suggestion,

    update fujitsu_spare_import
    set `date_spares`= str_to_date(date_temp,'%d/%m/%Y')
    where scan_type like '%R%'

    created a new column date_spares and its fine now.

    But should there be a way to update while ur using the load query?

    Anway thanks once again

Posting Permissions

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