2

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  |
+-------------+ 
  • Can you provide the definition of the table (and especially the column DogumTarihi)? Updating a column from one type to another is a bit awkward. – Lennart Apr 15 '17 at 8:47
  • DogumTarihi varchar(20)... I want it is datetime type. – cheftainjames Apr 15 '17 at 8:56
  • alter table Uye_Uye_Liste modify DogumTarihi datetime; ERROR 1292 (22007): Incorrect datetime value: '13-04-1996' for column 'DogumTarihi' at row 2 – cheftainjames Apr 15 '17 at 8:56
  • What does this string look like if you do a select DogumTarihi from Uye_Uye_Liste where ...? – Lennart Apr 15 '17 at 8:59
  • I wrote wrongly previous comment. select DogumTarihi from Uye_Uye_Liste where DogumTarihi = '13.04.1996'; +-------------+ | DogumTarihi | +-------------+ | 13-04-1996 | – cheftainjames Apr 15 '17 at 9:07
2

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
0

You have incorrectly formatted str_to_date function. As far as your date is stored using -, change it on this way:

set    DogumTarihi=str_to_date(DogumTarihi,'%d-%m-%Y')

NOTE: I've replaced '%d.%m.%Y' by '%d-%m-%Y'

create table Uye_Uye_Liste (id int, DogumTarihi varchar(20));
insert into Uye_Uye_Liste values(1, '13-04-1996');
update Uye_Uye_Liste 
set    DogumTarihi=str_to_date(DogumTarihi,'%d.%m.%Y') 
Incorrect datetime value: '13-04-1996' for function str_to_date

This works fine:

update Uye_Uye_Liste 
set    DogumTarihi=str_to_date(DogumTarihi,'%d-%m-%Y') 

Now you can change column type:

alter table Uye_Uye_Liste modify DogumTarihi datetime;
select * from Uye_Uye_Liste;
id | DogumTarihi        
-: | :------------------
 1 | 1996-04-13 00:00:00

dbfiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.