Change datetime format in mariadb

Database : Mariadb Platform : CentOS

Need to do Import data from a text file to table. Problem with DATETIME format.

Original date time format in test file : mmddYYYYHHiissmmm
Database default format : YYYYmmddHHiiss

LOAD DATA LOCAL 
INFILE '/home/test.txt' 
INTO TABLE cdr FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' 
(ID , APARTY , BPARTY , @T1, ENDTIME, DURATION, INTG, OUTTG, INRC, OUTRC) 
set STARTTIME = STR_TO_DATE(@T1,'%m-%d-%Y %H:%i:%s:%f'); 

After importing the values are showing NULL.

Answer

Assuming your example of ‘mmddYYYYHHiissmmm’ is correct, change ‘%m-%d-%Y %H:%i:%s:%f’ to ‘%m%d%Y%H%i%s%f’. Here’s a test:

mysql> SELECT STR_TO_DATE('12312015235959123', '%m%d%Y%H%i%s%f');
+----------------------------------------------------+
| STR_TO_DATE('12312015235959123', '%m%d%Y%H%i%s%f') |
+----------------------------------------------------+
| 2015-12-31 23:59:59.123000                         |
+----------------------------------------------------+
1 row in set (0.00 sec)

You cannot change the internal representation of DATETIME or TIMESTAMP.