How to convert text datatype to datetime in mysql?

Question

In mysql database,column name created.This "created " column is text datatype,I need to change this to datetime.Now this column have so many datas.Is it possible to convert it or?

Database look like

created
18-11-15 18:21:25

Expecting ouput is

created
2018-11-15 18:21:25

When am doing

ALTER TABLE invoices MODIFY created datetime

This query giving wrong data.its converting from 15-09-18 03:03:43 to 2015-09-18 03:03:43

Possible duplicate: https://stackoverflow.com/questions/14378003/convert-varchar-column-to-date-in-mysql-at-database-level — Taher A. Ghaleb, Nov 18 '18 at 03:01
Check this:https://stackoverflow.com/questions/15335880/how-to-change-varchar-type-to-datetime-using-alter-in-mysql — Taher A. Ghaleb, Nov 18 '18 at 03:07
@TeeKea when am doing "ALTER TABLE invoices MODIFY created datetime" This converting from 15-09-18 03:03:43 to 2015-09-18 03:03:43 — becool, Nov 18 '18 at 03:18

score 4 · Accepted Answer · answered Nov 18 '18 at 04:00

4

If the original data is not in MySQL Datetime format (YYYY-MM-DD HH:MM:SS), you cannot just change the column datatype from Varchar/Text to Date/Datetime. Otherwise, there will be an irreparable Data loss.

This will be a multi-step process. You will first need to convert the date string to MySQL date format (YYYY-MM-DD HH:MM:SS). We can use STR_TO_DATE() function for this.

Your sample date string (18-11-15 18:21:25) is basically in %y-%m-%d %T format. Following format specifiers can be used:

%d Day of the month as a numeric value (01 to 31)
%m Month name as a numeric value (00 to 12)
%y Year as a numeric, 2-digit value
%T Time in 24 hour format (hh:mm:ss)

The query to update the date would look as follows:

UPDATE invoices  
SET created = STR_TO_DATE(created, '%y-%m-%d %T');

Now, you can use Alter Table to change the data type from Text type to Datetime.

ALTER TABLE invoices 
MODIFY COLUMN created datetime;

answered Nov 18 '18 at 04:00

Madhur Bhaiya

28,155
10
49
57

how to convert 04-10-18 13:17:23 to 2018-10-04 13:17:23????it mean some datas are in the format of dd-mm-yy.so i need to convert it first.How to update these datas? – becool Nov 18 '18 at 08:51
when am doing UPDATE invoices SET created = STR_TO_DATE(created, '%y-%m-%d %T') WHERE invoiceID BETWEEN 2031 AND 2520; showing error #1411 - Incorrect datetime value: '2004-10-18 13:17:23' for function str_to_date – becool Nov 18 '18 at 08:54
before updating i need to change my data from 04-10-18 13:17:23 to 2018-10-04 13:17:23 – becool Nov 18 '18 at 08:55
@becool how come you have two different formats in the same column ? – Madhur Bhaiya Nov 18 '18 at 09:07
later i was changed my code to yyyy-dd-mm. before i was saving dd-mm-yy as text datatype.Becoz mysql date format accepts only yyyy-mm-dd.So that i changed – becool Nov 18 '18 at 09:11
Is there any way to interchange 04-10-18 13:17:23 to 2018-10-04 13:17:23 invoiceID BETWEEN 1834 AND 2031 – becool Nov 18 '18 at 09:12
@becool problem here is that how would you know 04-10-18 is 4 October 2018, or it is 18 October 2004 ? – Madhur Bhaiya Nov 18 '18 at 09:13
am confirmed that 2018-10-04.2018 only i have created database.There is no datas in 2004 – becool Nov 18 '18 at 09:16
@becool can you set up a https://www.db-fiddle.com/ with sample data covering both cases. I will need to do some testing, before giivng a final answer – Madhur Bhaiya Nov 18 '18 at 09:17
sir u want data or query? – becool Nov 18 '18 at 09:22
@becool click on Load Example button in the top menu bar. You will get the idea. Basically in the left side you add create table and insert into table statements to generate the example case (with sample data) and then click on save. It will generate a link. share the link with me. – Madhur Bhaiya Nov 18 '18 at 09:27
first three data format like dd-mm-yy and 4,5,6 are yy-mm-dd format..All data i need to convert like 2018-09-15 18:51:41 where id=1 and example two is like 2018-10-04 21:24:01 where id=4 – becool Nov 18 '18 at 09:42
@becool check the update query in this: https://www.db-fiddle.com/f/x2TAfgVjC88c173cSXboMp/1 – Madhur Bhaiya Nov 18 '18 at 09:57
1

sir sorry for the delay.Yeah its working now.Thanks a lot :) – becool Nov 18 '18 at 12:00
hi sir i need your help.i posted new question can u plz help me to do this? https://stackoverflow.com/questions/53382219/how-to-count-all-values-of-columns-where-two-conditions-are-satisfied-using-mysq – becool Nov 20 '18 at 03:52
When am downloading to excel sheet , I am getting $row['exclvat'] is 20.00 but excel sheet showing 20.If $row['exclvat'] is 80.98 then excel sheet showing 80.98.Only if 0.00 not showing.how can i change this in php code without manual changes in excel sheet? – becool Nov 20 '18 at 04:25
Hi @becool, I am unable to follow what you are asking for. It is always good to post a new question and share the link, I will try to answer (if question has enough details). – Madhur Bhaiya Nov 20 '18 at 06:16
sir already i have shared link.here it is https://stackoverflow.com/questions/53382219/how-to-count-all-values-of-columns-where-two-conditions-are-satisfied-using-mysq – becool Nov 20 '18 at 06:50
Can u able to check my new post?Can u reply to that post,i will explain it clearly – becool Nov 20 '18 at 07:23

score 0 · Answer 2 · answered Nov 18 '18 at 03:07

0

The best thing to do here is to not store your dates as text. Assuming you have already done this, we can cope by calling STR_TO_DATE to generate a bona fide date:

SELECT
    STR_TO_DATE(created, '%y-%m-%d %h:%i:%s') AS created_out
FROM yourTable;

Since the output you expect is standard date output, we can stop here and avoid also calling DATE_FORMAT to generate a different output.

answered Nov 18 '18 at 03:07

Tim Biegeleisen

502,043
27
286
360

sir i dont want select query.I just want to alter my datatype and change in column data also without removing data – becool Nov 18 '18 at 03:11
_Don't_ do this! Store proper dates instead! – Tim Biegeleisen Nov 18 '18 at 03:11
when am doing ALTER TABLE invoices MODIFY created datetime. This converting from 15-09-18 03:03:43 to 2015-09-18 03:03:43 – becool Nov 18 '18 at 03:18
Then create a new datetime column and populate it using the output of `STR_TO_DATE` above. Then, delete your original text date column. – Tim Biegeleisen Nov 18 '18 at 03:27
then how about the values in created column(text datatype)?This will be lost? – becool Nov 18 '18 at 03:30
You may retain the original text date column if you wish. It is up to you. – Tim Biegeleisen Nov 18 '18 at 03:32

score 0 · Answer 3 · answered Nov 18 '18 at 03:10

0

you want to convert output or database records ? for second you can use sql query :

UPDATE 'table_name' SET 'created' = CONCAT('20', 'created')

answered Nov 18 '18 at 03:10

Skeldar

153
13

What happens if the year be 1988? Then your solution would generate 2088. – Tim Biegeleisen Nov 18 '18 at 03:11
@Skeldar I just want to convert my database records and datatype also – becool Nov 18 '18 at 03:21
i got limited data but its super rare that he got "created" dates before 2k its possible but i will say ok my solution is kinda holes. – Skeldar Nov 18 '18 at 03:22

score 0 · Answer 4 · answered Nov 18 '18 at 04:01

You will need first to interchange the day with the year in the created column, as follows:

UPDATE invoices
SET created = CONCAT(SUBSTR(created, 7, 2), '-', SUBSTR(created, 4, 2), '-', SUBSTR(created, 1, 2));

Then, you convert the column to DATETIME, as follows:

ALTER TABLE invoices MODIFY created DATETIME;

Hope this helps.

How to convert text datatype to datetime in mysql?

4 Answers4