update database date values format using MY-SQL

Question

i have date stored in database on date column in dd-mm-yyyy format. This column data type is VARCHAR. i wanted to convert those stored dates in yyy-mm-dd format. i tried using this MYSQL query:

UPDATE status SET new_time_creation = STR_TO_DATE(date, '%Y-%m-%d');

but this does not convert the dates properly (eg. 30-05-2016 => 2030-05-20). please help me with this query or suggest best on even using php to update the date format. Thanks!

Why do you save such things in a varchar ? There are predefined field types for date / time. Simply use them and format the output with PHP. php.net/manual/de/datetime.format.php — Twinfriends
– Twinfriends, Commented Nov 14, 2017 at 12:43
@Twinfriends It looks like that is exactly what the OP is trying to do: Convert the wrong type to the correct one. — jeroen
– jeroen, Commented Nov 14, 2017 at 12:45
@jeroen As far as I understand the question he simply want to change the format how he saves the date, but I can't see any hint that he actually want to change the type of the field. — Twinfriends
– Twinfriends, Commented Nov 14, 2017 at 12:47
@Twinfriends Must be my positive outlook that I read it that way; copying the existing data to a new, better format :-) — jeroen
– jeroen, Commented Nov 14, 2017 at 12:48
@jeroen Well thats possible. We don't know as long as he doesn't answer. :P But may you're right, I don't know. — Twinfriends
– Twinfriends, Commented Nov 14, 2017 at 12:50

pr1nc3 · Accepted Answer · 2017-11-14 12:53:13Z

2

UPDATE status SET new_time_creation = STR_TO_DATE(date, '%d-%m-%Y');

try this. You need to format your date '%d-%m-%Y' in here. So basically in your code you want the order to be days-months-years but you update in the opposite order.

edited Nov 14, 2017 at 12:53

answered Nov 14, 2017 at 12:44

pr1nc3

8,3763 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

John Conde Over a year ago

"Try this" does not make for a good answer. Why should they use this code? How does it solve their problem?

pr1nc3 Over a year ago

I updated my answer with a few words in it to explain the problem + the solution. Thank you for pointing it out!

Nebiyu Deribe Over a year ago

@pr1nc3 as i said what i want the format to be is Y-m-d

pr1nc3 Over a year ago

based on your example you want to convert this date: 30-05-2016 so this is the reason your code does not work because this date has the format d/m/Y.

Nebiyu Deribe Over a year ago

@pr1nc3 so i want to update this date: 30-05-2016 to 2016-05-30 and i am asking how to do this using query.

kchason · Accepted Answer · 2017-11-14 13:28:31Z

0

UPDATE transaction SET new_time_creation = STR_TO_DATE(time_creation, '%Y-%m-%d');

STR_TO_DATE documentation.

edited Nov 14, 2017 at 13:28

kchason

2,88522 silver badges29 bronze badges

answered Nov 14, 2017 at 13:11

Shah Faisal

12 bronze badges

1 Comment

Toby Speight Over a year ago

Welcome to Stack Overflow! Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation would greatly improve its long-term value by showing why this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please edit your answer to add some explanation, including the assumptions you've made.

kchason · Accepted Answer · 2017-11-14 13:38:54Z

0

STR_TO_DATE should work, assuming you have the same format that you specify.

Assuming a basic table as specified below:

CREATE TABLE
Demo
(
date VARCHAR(10)
);

INSERT INTO `Demo` (`date`) VALUES ('30-05-2016');

You can obviously select itself, but you can also format it in whichever format serves your purpose using the DATE_FORMAT function.

SELECT
  `date` AS `OriginalFormat`,
  STR_TO_DATE(`date`, '%d-%m-%Y') AS `DateFormat`,
  DATE_FORMAT(STR_TO_DATE(`date`, '%d-%m-%Y'), '%m-%d-%Y') AS `CustomFormat`
FROM
  `Demo`

Returns:

OriginalFormat | DateFormat | CustomFormat
-------------------------------------------
30-05-2016     | 2016-05-30 | 05-30-2016

SQLFiddle: http://sqlfiddle.com/#!9/85c27c/3

answered Nov 14, 2017 at 13:38

kchason

2,88522 silver badges29 bronze badges

Collectives™ on Stack Overflow

update database date values format using MY-SQL

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related