Professional Web Applications Themes

date format trouble - MySQL

I get a data feed over which I have no influence WRT format. It comes with dates specified as mm/dd/yyyy. I'd wanted to use something like the following statement to load the data, but the date format is a problem. LOAD DATA INFILE 'C:/data/myfeedfile.txt' INTO TABLE mytable; I get an error message like "Incorrect date value: '11/22/1999' for column 'start_date' at row 1" Can I tell MySQL to interpret the date in the format provided as a date and be confident that it will do so properly? If so, how? In other words, if mm/dd/yyyy is not a valid date, ...

  1. #1

    Default date format trouble

    I get a data feed over which I have no influence WRT format.

    It comes with dates specified as mm/dd/yyyy.

    I'd wanted to use something like the following statement to load the
    data, but the date format is a problem.

    LOAD DATA INFILE 'C:/data/myfeedfile.txt' INTO TABLE mytable;

    I get an error message like "Incorrect date value: '11/22/1999' for
    column 'start_date' at row 1"

    Can I tell MySQL to interpret the date in the format provided as a date
    and be confident that it will do so properly? If so, how? In other
    words, if mm/dd/yyyy is not a valid date, as defined by MySQL, is there
    a way to convert it into a valid date within MySQL? I do not want to
    have to write an additional program to reformat the dates prior to
    loading the data into MySQL!

    Thanks,

    Ted

    Ted Guest

  2. #2

    Default Re: date format trouble


    mysql> select cast('11/20/2006' as DATE) as T_DATE;
    +----------------+
    | T_DATE |
    +----------------+
    | 11/20/2006 |
    +----------------+
    >From the docs:
    mysql> LOAD DATA INFILE '/tmp/bit_test.txt'
    -> INTO TABLE bit_test (var1) SET b= CAST(var1 AS SIGNED);

    you could (not tested) maybe use set date_col=cast(var1 as DATE);

    onedbguru@firstdbasource.com Guest

  3. #3

    Default Re: date format trouble

    This didn't work for me. I submitted ( using MySQL v 5.0.16 and the
    MySQL Query Browser) "select cast('11/20/2006' as DATE) as T_DATE;"
    exactly as you show it, and I get an error # 1292, "truncated incorrect
    datetime value: '11/20/2006'"

    In the resultset, the column name is right, but the value is set as
    null.

    If I use the second statement you suggest, I suppose I will have to
    list all of the columns, won't I. It is a rather large table!

    BYW: Using the chm version of the manual, I could not find anything on
    "cast". Is that a built in function? If so, where will I find more
    information about it? If it is builtin, can I create a UDT which
    breaks the date format I am receiving into the day, month and year
    components (perhaps using regular expressions?), and then overload cast
    to convert that UDT into a proper DATE object? Or does SQL not support
    UDTs?

    Thanks,

    Ted

    Ted Guest

  4. #4

    Default Re: date format trouble

    Ted wrote:
    > LOAD DATA INFILE 'C:/data/myfeedfile.txt' INTO TABLE mytable;
    >
    > I get an error message like "Incorrect date value: '11/22/1999' for
    > column 'start_date' at row 1"
    >
    > Can I tell MySQL to interpret the date in the format provided as a date
    > and be confident that it will do so properly? If so, how? In other
    > words, if mm/dd/yyyy is not a valid date, as defined by MySQL, is there
    > a way to convert it into a valid date within MySQL? I do not want to
    > have to write an additional program to reformat the dates prior to
    > loading the data into MySQL!
    No, there's no way to tell MySQL that mm/dd/yyyy is a valid date format
    on input. See [url]http://dev.mysql.com/doc/refman/5.0/en/datetime.html[/url] for
    valid input formats.

    Another solution is to create a temporary table for the LOAD DATA
    destination, and make the column for your date value a CHAR instead.
    Then copy the data to your real destination table, and use the
    STR_TO_DATE() function to interpret the mm/dd/yyyy string as a date.

    INSERT INTO real_table (a, b, c, datecol)
    SELECT a, b, c, STR_TO_DATE(datecol_as_string, '%m/%d/%Y')
    FROM temp_table;

    See [url]http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html[/url]
    for docs on the STR_TO_DATE() function.

    Regards,
    Bill K.
    Bill Karwin Guest

  5. #5

    Default Re: date format trouble

    Ted wrote:
    > BYW: Using the chm version of the manual, I could not find anything on
    > "cast". Is that a built in function? If so, where will I find more
    > information about it?
    For what it's worth, CAST() docs are here:
    [url]http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html[/url]

    But I don't think that CAST() will solve this problem.

    I suppose it might work to do this:

    mysql> LOAD DATA INFILE '/tmp/bit_test.txt'
    -> INTO TABLE bit_test (var1)
    -> SET datecol = STR_TO_DATE(var1, '%m/%d/%Y');
    > If it is builtin, can I create a UDT which
    > breaks the date format I am receiving into the day, month and year
    > components (perhaps using regular expressions?), and then overload cast
    > to convert that UDT into a proper DATE object? Or does SQL not support
    > UDTs?
    No, MySQL does not support UDT's.

    Regards,
    Bill K.
    Bill Karwin Guest

  6. #6

    Default Re: date format trouble

    >This didn't work for me. I submitted ( using MySQL v 5.0.16 and the
    >MySQL Query Browser) "select cast('11/20/2006' as DATE) as T_DATE;"
    >exactly as you show it, and I get an error # 1292, "truncated incorrect
    >datetime value: '11/20/2006'"
    I don't think a cast will work here, as the date isn't in the correct
    format. However, something like:

    str_to_date('11/20/2006', '%m/%d/%Y')

    should convert the date to a format MySQL likes.

    Gordon L. Burditt
    Gordon Burditt Guest

  7. #7

    Default Re: date format trouble

    OK, putting it all together, I tried the following:

    LOAD DATA INFILE 'C:/data/fund.txt' INTO TABLE fund
    ('key',var1,'fund_name','associated_index','group _name','fund_type_name',

    'investment_objective','investment_strategy','deta iled_classification',
    'rrsp_min_invest',
    'non_rrsp_min_invest','pac_plan','swp_plan','swp_m in_balance','legal_status','trust_type','currency' ,
    'how_sold','rrsp_eligible','resp_eligible',
    'load','no_load_fund','choice_of_front_or_back_fee ',
    'max_front_end_percentage','max_back_end_percentag e','back_end_fee_applied_to','management_fee_perce nt',
    'available_in_province')
    SET start_date=STR_TO_DATE(var1,'%m/%d/%y');

    MySQL Query Browser displays an error, which I assume is from MySQL v
    5.0.19 (a different machine that the other I used), indicatng there is
    an error on the second line. It doesn't say what the error is though.
    All it gives is a number '1064'

    There is no mention of there being a problem with my use of
    STR_TO_DATE.

    Any idea as to what may be awry?

    Ted

    Ted Guest

  8. #8

    Default Re: date format trouble

    >OK, putting it all together, I tried the following:
    >
    >LOAD DATA INFILE 'C:/data/fund.txt' INTO TABLE fund
    >('key',var1,'fund_name','associated_index','grou p_name','fund_type_name',
    Quote field names with backquotes, not single quotes.

    Gordon L. Burditt
    Gordon Burditt Guest

  9. #9

    Default Re: date format trouble

    Still no joy!

    The statement now looks like:

    LOAD DATA INFILE 'C:/data/fund.txt' INTO TABLE fund
    (`key`,date_as_string,`fund_name`,`associated_ind ex`,`group_name`,`fund_type_name`,

    `investment_objective`,`investment_strategy`,`deta iled_classification`,`rrsp_min_invest`,

    `non_rrsp_min_invest`,`pac_plan`,`swp_plan`,`swp_m in_balance`,`legal_status`,`trust_type`,`currency` ,

    `how_sold`,`rrsp_eligible`,`resp_eligible`,`load`, `no_load_fund`,`choice_of_front_or_back_fee`,

    `max_front_end_percentage`,`max_back_end_percentag e`,`back_end_fee_applied_to`,`management_fee_perce nt`,
    `available_in_province`)
    SET start_date=STR_TO_DATE(date_as_string,'%m/%d/%y');

    This gets to examine the data, so I guess the syntax is OK. But I
    still get an error about the date. This time, the error is "Truncated
    incorrect date value: "11/22/1999"

    Any more ideas?

    Is MySQL trying to interpret the date value before the call to
    STR_TO_DATE()?

    What is likely to happen if I add a column called date_as_string
    instead of trying to load it into var1? Do the names in the list in
    the LOAD DATA statement have to be provided in the order the
    corresponding columns were created in the table? If not, I can load
    the data first, and that with the data going into a column of type
    char[10], and then run an UPDATE statement in which the SET start_date
    clause above is invoked. Right?

    Ted

    Ted Guest

  10. #10

    Default Re: date format trouble

    Ted wrote:
    > Is MySQL trying to interpret the date value before the call to
    > STR_TO_DATE()?
    Yeah, that's what it appears like.
    > What is likely to happen if I add a column called date_as_string
    > instead of trying to load it into var1?
    That's what I would try next. I haven't had experience doing this
    precise solution, but it sounds good.

    Regards,
    Bill K.
    Bill Karwin Guest

  11. #11

    Default Re: date format trouble

    > SET start_date=STR_TO_DATE(date_as_string,'%m/%d/%y');

    Dates have 4 digits in your data, right? %y and %Y are different.

    gordon/(none)> select str_to_date('11/29/1999', '%m/%d/%y');
    +---------------------------------------+
    | str_to_date('11/29/1999', '%m/%d/%y') |
    +---------------------------------------+
    | 2019-11-29 |
    +---------------------------------------+
    1 row in set, 1 warning (0.00 sec)

    gordon/(none)> show warnings;
    +---------+------+----------------------------------------------+
    | Level | Code | Message |
    +---------+------+----------------------------------------------+
    | Warning | 1292 | Truncated incorrect date value: '11/29/1999' |
    +---------+------+----------------------------------------------+
    1 row in set (0.00 sec)

    gordon/(none)> select str_to_date('11/29/1999', '%m/%d/%Y');
    +---------------------------------------+
    | str_to_date('11/29/1999', '%m/%d/%Y') |
    +---------------------------------------+
    | 1999-11-29 |
    +---------------------------------------+
    1 row in set (0.01 sec)

    >This gets to examine the data, so I guess the syntax is OK. But I
    >still get an error about the date. This time, the error is "Truncated
    >incorrect date value: "11/22/1999"
    %Y and %y are different.
    >instead of trying to load it into var1? Do the names in the list in
    >the LOAD DATA statement have to be provided in the order the
    >corresponding columns were created in the table?
    I doubt it. That would make listing the names rather pointless.
    >If not, I can load
    >the data first, and that with the data going into a column of type
    >char[10], and then run an UPDATE statement in which the SET start_date
    >clause above is invoked. Right?
    Yes. But fix your format first.

    Gordon L. Burditt
    Gordon Burditt Guest

Similar Threads

  1. I'm having trouble with a Text Field and a Time Format
    By dweinin in forum Adobe Acrobat Macintosh
    Replies: 0
    Last Post: August 13th, 06:00 PM
  2. Date Format in Com+
    By news.microsoft.com in forum ASP Components
    Replies: 0
    Last Post: July 12th, 03:41 PM
  3. Format a date
    By johnsonPA in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: October 3rd, 08:09 PM
  4. Date Format pb
    By Franck in forum ASP.NET Web Services
    Replies: 1
    Last Post: April 21st, 05:55 PM
  5. converting date into database date format(newbie)
    By Binay Agarwal in forum PHP Development
    Replies: 2
    Last Post: August 29th, 01:09 PM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139