mysql - STR_TO_DATE() Returning NULL -


i've looked @ other questions regarding str_to_date() , solutions aren't working case. i've read , searched elsewhere no avail.

i'm importing many rows of following xml:

<game>   <id>21</id>    <gametitle>final fantasy xiii</gametitle>    <releasedate>03/09/2010</releasedate>  </game>  <game>   <id>31</id>    <gametitle>heavenly sword</gametitle>    <releasedate>09/12/2007</releasedate>  </game>

with following sql statement:

use test; load xml local infile 'c:\\xampp\\mysql\\data\\test.xml' replace table games rows identified '<game>' set releasedate = str_to_date(@releasedate, '%c/%e/%y');

gametitle , id importing perfectly. i've tried using %m , %d , have ensured releasedate column date datatype. rows writing null in releasedate column.

mysql returning 

2466 row(s) affected, 64 warning(s): 1265 data truncated column 'releasedate'  @ row 1 1265

thank in advance help. stackoverflow has solved every problem i've ever had without having ask.

you'd need assign variable you're using str_to_date, like

load xml local infile 'c:\\xampp\\mysql\\data\\test.xml' replace table games (id, gametitle, @var1) rows identified '<game>' set releasedate = str_to_date(@var1, '%c/%e/%y');

note syntax set in load xml described in syntax load data


Comments

Post a Comment

Popular posts from this blog

matlab - Deleting rows with specific rules -

i got 20*3 cell array , need delete rows contains "137", "2" , "n:t" origin data: 't' '' '' 'np(*)' '' '' [ 137] '' '' [ 2] '' '' 'are' 'and' 'np(fcc_a1#1)' '' '' '1:t' [ 1200] [0.7052] '' [1.2051e+03] [0.7076] '' 'are' 'and' 'np(fcc_a1#3)' '' '' '2:t' [ 1200] [0.0673] '' [1.2051e+03] [0.0671] '' 'are' 'and' 'np(m23c6)' '' '' '3:t' [ 1200] [0.227...
Read more

php - MySQLi multi_query results for later use -

i using mysqli multi_query work several select statemets @ time. know how handle results, able use them later in code. example: <?php //connection stuff $query = "select name, surname database1;"; $query.= "select car, year, type database2 carid='1';"; $query.= "select product, price database3;"; if ($mysqli->multi_query($query)) { if($result = $mysqli->store_result()) { while($row = $result->fetch_row()) { --> here? } } } ?> <html> <div id='persona'> <?php foreach() { --> print name + surname } ?> </div> <div id='cars'> <?php foreach() { --> print car + year + type } ?> </div> <div id='product'> <?php foreach() { --> print product + price } ?> </div> </html> one more thing, prepared statements not possible when using multiple_query, right? there no benefit in putting unrelated queries in 1 multi ...
Read more