Change datetime format in mariadb

Question

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.

I count at least 3 previous question on StackOverflow related to your request, e.g. [**Mysql: Setup the format of DATETIME to 'DD-MM-YYYY HH:MM:SS ...**](http://stackoverflow.com/questions/8338031/mysql-setup-the-format-of-datetime-to-dd-mm-yyyy-hhmmss-when-creating-a-tab). Just substitute `mysql` for `mariadb` in your search. — David C. Rankin, Feb 17 '16 at 04:17
Please update your question with information about what you have already tried and (if possible) a small excerpt from your input textfile so we can reproduce the error and try different commands on our own. — bratkartoffel, Feb 17 '16 at 06:58

score 1 · Accepted Answer · answered Feb 18 '16 at 22:08

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.

Change datetime format in mariadb

1 Answers1