2

I have received a Mysql table with customer data which is pretty badly structured, almost all the fields are TEXT. To prevent losing data I have created another table where I am trying to import the columns in a correct and useful manner. Splitting Full_name into name and surname columns work great but when I try to convert the field created_time containing dd/mm/yy date to DATETIME it shows wrong data eg. 10/10/18 to 2010-10-18.

I have resorted to creating another field created_time_old, copying the text data there and then converting it via STR_TO_DATE as some other answer suggested. However, I can only put strings there, when I put the whole column it just gives me:

1411 - Incorrect datetime value: '' for function str_to_date.

I assume that there is another way/function that will manage to do it but I am not very experienced when it comes to SQL. Moreover, if you have any other suggestions when it comes to my code, please post them :)

My code is below

TRUNCATE TABLE Facetel_bazaPGS;
INSERT INTO Facetel_bazaPGS (id,created_time_old,campaign_name,email,Imię,Nazwisko,`phone_number`,platform,Time_added)

SELECT `id`,created_time,`campaign_name`,`email`,substring_index(`full_name`, ' ',1 ),substring(`full_name` from INSTR(`full_name`, ' ') + 1),`phone_number`,`platform`,Time_added

FROM `Facetel_bazaPGS_input`;
UPDATE Facetel_bazaPGS

SET platform = Replace(REPLACE(platform, 'fb', 'Facebook') , 'ig', 'Instagram');

UPDATE Facetel_bazaPGS
SET created_time = STR_TO_DATE(`created_time_old`, '%d/%m/%y');

EDIT#1: Adding sample data (can't give real data because of GDPR)

+------------------+--------------+---------------+--------------------+--------------+---------------------+----------+---------------------+
|        id        | created_time | campaign_name |       email        |  full_name   |    phone_number     | platform |     Time_added      |
+------------------+--------------+---------------+--------------------+--------------+---------------------+----------+---------------------+
| 1010334092505681 | 10/10/18     | leady         | samplemail@mail.eu | Name         | your_typical_number | ig       | 2018-10-11 08:29:45 |
| 1010457652493325 | 10/10/18     | leady         | samplemail@mail.eu | Name Surname | your_typical_number | ig       | 2018-10-11 08:29:45 |
| 1010470612492029 | 10/10/18     | leady         | samplemail@mail.eu | Name Surname | your_typical_number | fb       | 2018-10-11 08:29:45 |
+------------------+--------------+---------------+--------------------+--------------+---------------------+----------+---------------------+
Community
  • 1
  • 1
Marcjan Dremo
  • 21
  • 2
  • 1
    Please show some sample data for the `Facetel_bazaPGS_input` table. My guess is that you might have some bad date strings in your table. Your current logic should be working, assuming all data is good. – Tim Biegeleisen Oct 11 '18 at 09:59
  • Thank you, sample data added into the main post :) – Marcjan Dremo Oct 11 '18 at 10:14
  • That sample data doesn't help unfortunately. I gave an answer below which might help you to sort things out. – Tim Biegeleisen Oct 11 '18 at 10:15
  • You obviously have one or more row(s) with empty value in column `created_time` field of `Facetel_bazaPGS_input` table. – Madhur Bhaiya Oct 11 '18 at 10:18
  • read [this](https://stackoverflow.com/a/14962253/2067753) please. `SET @@SESSION.sql_mode='ALLOW_INVALID_DATES';` – Paul Maxwell Oct 11 '18 at 10:38
  • After adding SET @@SESSION.sql_mode='ALLOW_INVALID_DATES'; it no longer gives an error but the STR_TO_DATE part returns no data, created_time in the new table is NULL. – Marcjan Dremo Oct 11 '18 at 11:17

2 Answers2

0

This answer is a speculation, but it attempts to get to the root cause of your 1411 error coming from the call to STR_TO_DATE. We can try running the following query to detect malformed date strings in your Facetel_bazaPGS_input source table:

SELECT *
FROM Facetel_bazaPGS_input
WHERE created_time NOT REGEXP '^[0-9]{2}/[0-9]{2}/[0-9]{2}$';

This would return any record not having a created_time in the format you expect. If you don't see an empty result set, then you can fix the date strings.

Note: Just now I can see that your error message seems to be saying that some created_time values are either empty string or NULL. In either case, the above query should flush out such records.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Sadly it returns no records at all. Also, I checked it manually and all the dates are in this format, when I exported the file in .csv the fields have the value of '10/10/18, could this be the case? – Marcjan Dremo Oct 11 '18 at 10:35
  • The state of the data in the CSV file does not really matter; we need to find the bad records in MySQL _after_ you have imported. – Tim Biegeleisen Oct 11 '18 at 10:37
  • Invalid dates such as 30/02/2011 can be easy to miss - see my comment above – Paul Maxwell Oct 11 '18 at 10:40
  • @Used_By_Already That might be the problem. I assumed that any dates, if present, were valid. I'm starting to think that the OP's problem is not even reproducible. If the dates themselves might be bad, I can only imagine what the source of data is. – Tim Biegeleisen Oct 11 '18 at 10:44
  • As stated before, this query was run and returned no records. – Marcjan Dremo Oct 11 '18 at 10:49
  • the regular expression won't detect invalid dates, it just looks for the pattern of nn/nn/nn e.g. 31/02/18 would pass the regexp, but it isn't a valid date – Paul Maxwell Oct 11 '18 at 11:02
  • @Used_By_Already Um...I know that :-) ... and I said in an earlier comment that invalid dates might not be what is happening here. Again, if so, the data source must be very questionable. – Tim Biegeleisen Oct 11 '18 at 11:04
  • @TimBiegeleisen sorry, previous comment was in reply to `As stated before, this query was run and returned no records.` "my bad" forgot to direct it – Paul Maxwell Oct 11 '18 at 11:11
  • I thank you guys for your help so far, I am still thinking of other ways to solve this problem but your responses are most helpful. @TimBiegeleisen what do you mean about the questionability of the data source? I can assure you that the data received is legal if that is the question :) – Marcjan Dremo Oct 11 '18 at 11:24
  • LOL...not that (I hope). Used By was suggesting that maybe you have dates which fit the format, but they are bogus, e.g. `February 30, 2018`, or `30/02/18`. This might also generate the error you are seeing. I find it unlikely, if your data source is clean, however. – Tim Biegeleisen Oct 11 '18 at 11:25
0

Thank you again for all your suggestions, I have given up, changed the format of the column in Excel, imported again and asked for any future imputs to have YYYY-MM-DD format in the created_time column.

Marcjan Dremo
  • 21
  • 2