Mysql – Truncated Incorrect date value string with comma

MySQL

I have problem in date columns to insert from varchar column to date column.
I used str_to_date and Date_Format functions but it doesn't solved. How Can I

Simple Example:

update Uye_Uye_Liste 
set    DogumTarihi=str_to_date(DogumTarihi,'%d.%m.%Y') 
where  DogumTarihi is not null;

ERROR 1292 (22007): Truncated incorrect date value: '23.07.2002 00:00:00'

alter table Uye_Uye_Liste modify DogumTarihi datetime;

ERROR 1292 (22007): Incorrect datetime value: '13-04-1996' for column 'DogumTarihi' at row 2

select DogumTarihi from Uye_Uye_Liste where DogumTarihi = '13.04.1996'; 

+-------------+ 
| DogumTarihi | 
+-------------+ 
| 13-04-1996  |
+-------------+

Best Answer

My guess is that you have one or more malformed values. Given the information you provided:

create table Uye_Uye_Liste ( DogumTarihi varchar(20) ) engine = innodb;
insert into Uye_Uye_Liste (DogumTarihi) values ('13.04.1996');

update Uye_Uye_Liste set DogumTarihi =  str_to_date(DogumTarihi,'%d.%m.%Y');
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


alter table Uye_Uye_Liste modify DogumTarihi datetime;
Query OK, 1 row affected (0.06 sec)                
Records: 1  Duplicates: 0  Warnings: 0

So if every row corresponds to this pattern you should be alright. I suspect that some rows does not, focus on identifying and transforming those rows.

For the row given in the original query it is sufficient to to manually truncate the row using substr:

insert into Uye_Uye_Liste (DogumTarihi) values ('23.07.2002 00:00:00');
update Uye_Uye_Liste set DogumTarihi = str_to_date(substr(DogumTarihi,1,10),'%d.%m.%Y');
select * from Uye_Uye_Liste;
+-------------+
| DogumTarihi |
+-------------+
| 1996-04-13  |
| 2002-07-23  |
+-------------+
2 rows in set (0.00 sec)


alter table Uye_Uye_Liste modify DogumTarihi datetime;
Query OK, 2 rows affected (0.05 sec)               
Records: 2  Duplicates: 0  Warnings: 0

Related Solutions

MySQL 5.6 DateTime Incorrect datetime value: ‘2013-08-25T17:00:00+00:00’ with Error Code 1292

'2013-08-25T17:00:00+00:00'

This is a valid iso-8601 datetime value, but it is not a valid MySQL datetime literal. On that point, the developer is incorrect.

The documentation explains what ALLOW_INVALID_DATES does:

Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31.

In other words, 2013-02-31 would be a permissible date if allow_invalid_dates is set. This option does not do anything when the date or datetime isn't even in a valid format for MySQL.

The +00:00 is the timezone offset from UTC. In this case, the time expressed is in UTC, so the offset is zero hours, zero minutes.

Your workaround would be to remove the STRICT_TRANS_TABLES from the sql_mode that is a default in the config file created during the MySQL 5.6 installation process... you need to carefully consider the implications of changing this, but it does allow the data to go in.

mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode                                 |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into datetimetest(dt) values ('2013-08-26T12:00:00+00:00');
ERROR 1292 (22007): Incorrect datetime value: '2013-08-26T12:00:00+00:00' for column 'dt' at row 1

-- remove STRICT_TRANS_TABLES -- note that executing this only removes it for your
-- current session -- it does not make a server-wide config change

mysql> set @@sql_mode='no_engine_substitution';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

-- now MySQL will accept the invalid value, with a warning

mysql> insert into datetimetest(dt) values ('2013-08-26T12:00:00+00:00');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'dt' at row 1 |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

-- the value did get inserted, but the time zone information was lost:

mysql> select * from datetimetest;
+----+---------------------+
| id | dt                  |
+----+---------------------+
|  1 | 2013-08-26 12:00:00 |
+----+---------------------+
1 row in set (0.00 sec)

Mysql – Incorrect datetime value for str_to_date

Try it:

UPDATE subscribers SET srtdt = STR_TO_DATE(StartDate, '%Y-%m-%d');

Best Answer

Related Solutions

MySQL 5.6 DateTime Incorrect datetime value: ‘2013-08-25T17:00:00+00:00’ with Error Code 1292

Mysql – Incorrect datetime value for str_to_date

Related Question