Importing Leads with /18 as the year comes in as the year 0018

So this is probably a simple solution and I just don’t know where to find it.

Sometimes people at our office bulk import leads through a .csv file.

The date will be sometimes written as 4/10/18 for example, and when it gets imported into suiteCRM the year is read as April 10th, 0018.

One solution is to reformat the column in Excel as m/d/yyyy instead of the default way excel handles dates.

I was just wondering if there is something that can be changed on the suiteCRM side for it to be able to understand a 2 digit date like /18 or /19 as 2018 or 2019.

bump lol

I have never tried it, however, in config.php there is a section for date formats:

  'date_formats' => 
  array (
    'Y-m-d' => '2010-12-23',
    'm-d-Y' => '12-23-2010',
    'd-m-Y' => '23-12-2010',
    'Y/m/d' => '2010/12/23',
    'm/d/Y' => '12/23/2010',
    'd/m/Y' => '23/12/2010',
    'Y.m.d' => '2010.12.23',
    'd.m.Y' => '23.12.2010',
    'm.d.Y' => '12.23.2010',
  ),
  'datef' => 'm/d/Y',

I assume that, if you edit this section yuo should be able to achieve what you are looking for.

For example, you could modify it to:

  'date_formats' => 
  array (
    'Y-m-d' => '2010-12-23',
    'm-d-Y' => '12-23-2010',
    'd-m-Y' => '23-12-2010',
    'Y/m/d' => '2010/12/23',
    'm/d/Y' => '12/23/2010',
    'd/m/Y' => '23/12/2010',
    'Y.m.d' => '2010.12.23',
    'd.m.Y' => '23.12.2010',
    'm.d.Y' => '12.23.2010',
    'd/m/y' => '23/12/10',
  ),
  'datef' => 'd/m/y',

Instead of doing this in config.php, which is vulnerable to rebuilds of the file, you may do it in config_override.php.

However in this file you have to enter it in a different way:


$sugar_config['date_formats']['d/m/y'] = '23/12/10';
$sugar_config['datef'] = 'd/m/y';

Another solution would be to correct it in the database. I am not giving you the sql statement because I am not sure of the effects it may have. This has to be done with extreme care not to change irreversibly values that shouldn’t be changed.

My last comment is that, if you want a really functioning system, the year has to be the full year (yyyy, eg 2018) and not just made with ywo digits (yy eg 18) only. This is because your searches, reports, sorting, etc, will fail if your data is inconsistent.

Just imagine sorting 17, 18, 2017, 2018 or searching for something after 01/01/2018: you will not get those with year 18!!!

Thank you for the information and I agree that having a 2 digit year is terrible, but what i was hoping to achieve was having it be typed in as /18 and then having it converted to 2018. For now I have been exporting and then re-importing the leads with the year 0018 as 2018, only a few minutes of work but it really should be entered in correctly to begin with. Its just excel can be a pain in the ass sometimes.

Have you tried changing the settings in config_override.php?

Did it work?