0

Here is a line from the file I am loading.

"dev"|"APSET3454"|"apset3454.xxx.xxx.xxx"|"TWS01"|31111|True|False|False|"1"|True|"Win2012R2 64 bit ESC:712482-2267174 IEMID:84520"|"2017-12-20T15:06:15.390+0000"|"WINDOWS"|"9.4.0.04"|"FTA"|"America/Chicago"|2602|"2019-02-27T13:15:08.000+0000"|30|0|""|True|True|False|False|"LINKED"|True

And here is the load command I am using. Seems to work fine except that the last field is always comming in as a zero(false)

LOAD DATA LOCAL INFILE "/autohome/astools/Programs/FTA_api/Workdir//nm2.20190305103959.csv" 
INTO TABLE  fta_status.fta_info COLUMNS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' 
ESCAPED BY '"' LINES TERMINATED BY '\n' 
(TWSINSTANCE,CPUID,NODENAME,FTA_DOMAIN,TCPPORT,@AUTOLINK,@BEHINDFIREWALL,@FULLSTATUS, 
SERVER,@INCLUDEINPLAN,DESCRIPTION,@MODIFIEDDATETIME,OS,VERSION,TYPE,TIMEZONE, 
SYMRUNNUMBER,@TIMESTARTED,FTA_LIMIT,FENCE,ACCESSMETHOD,@TCPLINK,@JOBMANUP,@WRITERUP, 
@WRITERDS,LINKSATUS,@WORKSTATIONINPLAN)  
SET AUTOLINK = (@AUTOLINK = 'TRUE'), 
BEHINDFIREWALL = (@BEHINDFIREWALL = 'TRUE'),FULLSTATUS = (@FULLSTATUS = 'TRUE'), 
INCLUDEINPLAN = (@INCLUDEINPLAN = 'TRUE'), 
MODIFIEDDATETIME = DATE_FORMAT(STR_TO_DATE(@MODIFIEDDATETIME,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d %H:%i:%s'), 
TIMESTARTED = DATE_FORMAT(STR_TO_DATE(@TIMESTARTED,'%Y-%m-%dT%H:%i:%s'),'%Y-%m-%d %H:%i:%s'), 
TCPLINK = (@TCPLINK = 'TRUE'),JOBMANUP = (@JOBMANUP = 'TRUE'), 
WRITERUP = (@WRITERUP ='TRUE'),WRITERDS = (@WRITERDS = 'TRUE'), 
WORKSTATIONINPLAN = (@WORKSTATIONINPLAN = 'TRUE');

I do get warnings about the 2 datetime fields

Level: Warning
Code: 1292 Message: Truncated incorrect datetime value: '2017-12-20T15:06:15.390+0000'

So if someone would like to help with the date_format STR_TO_DATE conversion as well :-)

0

Your instinct is to use STR_TO_DATE() like you did to convert the string format but will not work "correcty" because of the warning.

Query #1

SELECT STR_TO_DATE('2019-02-27T13:15:08.000+0000','%Y-%m-%dT%H:%i:%s');

| STR_TO_DATE('2019-02-27T13:15:08.000+0000','%Y-%m-%dT%H:%i:%s') |
| --------------------------------------------------------------- |
| 2019-02-27 13:15:08                                             |

Query #2

SHOW WARNINGS;

| Level   | Code | Message                                                            |
| ------- | ---- | ------------------------------------------------------------------ |
| Warning | 1292 | Truncated incorrect datetime value: '2019-02-27T13:15:08.000+0000' |

So you will need to be a little more creative with SUBSTRING(), CONCAT() and a CAST()

Query

SELECT
  CAST (
    CONCAT(
       SUBSTRING('2019-02-27T13:15:08.000+0000', 1, 10)
     , ' '
     , SUBSTRING('2019-02-27T13:15:08.000+0000', 12, 8)
   )
   AS DATETIME  
  ) AS result;

Result

| result              |
| ------------------- |
| 2019-02-27 13:15:08 |

see demo

1
  • Thanks Raymond, This ended up working for me DATE_FORMAT(STR_TO_DATE(@MODIFIEDDATETIME,'%Y-%m-%dT%H:%i:%s.%f+0000'),'%Y-%m-%d %H:%i:%s') Also I found my issue the csv I was importing did not have a tailing | after the last field so that was causing an issue. Once I added that it worked as expected. Mar 5 '19 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.