1

I have a csv file which I want to load into a table. It has a column with the following date format:

'%d-%b-%y'

For example: '01-Dez-18'. If I load this file considering the format:

IF(@entry_dt = '',NULL,STR_TO_DATE(@entry_dt, '%d-%b-%y'))

I get a warning:

Incorrect datetime value: '01-Dez-12' for function str_to_date

This is due to the format of the column which is de_CH but the local and global settings are en_US. That is the reason why 01-Jan-12 was loaded correctly. Changing the local setting

SET lc_time_names = 'de_CH';

does not work since:

lc_time_names does not affect the STR_TO_DATE() or GET_FORMAT() function

(see here).

Is there a way to load the date column considering German/Swiss notation?

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
giordano
  • 2,227
  • 3
  • 29
  • 49

1 Answers1

1

The Monthname is in German (Dez). If you change it to Dec it works.

if you want to get the german names you must install the locale Plugin

1) see if already installed:

show plugins;

2) install the Plugin

INSTALL SONAME 'locales';

3) set local or set it global in the my.ini

SET  lc_time_names = 'de_DE';
select STR_TO_DATE('01-Dec-18', '%d-%b-%y');
Bernd Buffen
  • 12,768
  • 2
  • 20
  • 31
  • Ì did step 2) and 3) but it does not work. In the documentation it was mentioned that `lc_times_names` does not affect `STR_TO_DATE`. Maybe if I change globally? How can I change globally? – giordano Mar 15 '19 at 17:17