Find and replace datetime in MySql

7webcreator
Topic Author
Offline

3 years 5 months ago #123456 by 7webcreator

Find and replace datetime in MySql was created by 7webcreator

Hello,

I know how to replace a text value of one column with another text value for a selected number of rows.

UPDATE `table_name`
SET `field_name` = replace(same_field_name, 'unwanted_text', 'wanted_text')

However, this syntax does not work when I try it for a datetime field type. Can someone pleae give me the right syntax?

Thank you

Please Log in to join the conversation.

Nick
Offline

3 years 5 months ago #123465 by Nick

Replied by Nick on topic Find and replace datetime in MySql

Hi 7webcreator,

Something like the following should work well:

UPDATE `table_name`
SET `field_name` = `19/02/2016 9:30:00.000` WHERE `field_name` = `19/02/2016 9:30:00.000`

If you want it between (inclusive) certain dates, you can use something similar to:

UPDATE `table_name`
SET `field_name` = `19/02/2016 9:30:00.000` WHERE `field_name` BETWEEN `19/02/2016 9:30:00.000` AND `20/02/2016 9:30:00.000`

or:

UPDATE `table_name`
SET `field_name` = `19/02/2016 9:30:00.000` WHERE `field_name` >= `19/02/2016 9:30:00.000` AND `field_name` <=`20/02/2016 9:30:00.000`

Hope this helps! Let us know if you have any questions and we'll be glad to answer.

Kind regards,
Nick

Please Log in to join the conversation.

7webcreator
Topic Author
Offline

3 years 5 months ago #123487 by 7webcreator

Replied by 7webcreator on topic Find and replace datetime in MySql

Hi Nick,

Many thanks for that. I tried it with no success, I'm afraid. I got the below error message:
#1054 - Unknown column '2016-06-17 9:30:00.000' in 'where clause'

UPDATE `orders`
SET `OpDate` = `2016-06-21 9:30:00.000` WHERE `Opdate` = `2016-06-17 9:30:00.000`

I also removed the time or use %LIKE% with no success. Any other suggestions?

Please Log in to join the conversation.

Nick
Offline

3 years 5 months ago #123496 by Nick

Replied by Nick on topic Find and replace datetime in MySql

Hi 7webcreator,

You have OpDate and Opdate (lower case d). Please double check and make sure your capitalizations are correct in both instances.

Let us know what result you get.

Kind regards,
Nick

Please Log in to join the conversation.

7webcreator
Topic Author
Offline

3 years 5 months ago #123503 by 7webcreator

Replied by 7webcreator on topic Find and replace datetime in MySql

Hi Nick,

Sorry, it was just a typo when I re transcribed here. I did enter OpDate for both and I still have the same error message
#1054 - Unknown column '2016-06-17' in 'where clause'
UPDATE `orders`
SET `OpDate` = `2016-06-21` WHERE `OpDate` = `2016-06-17`

Kind regards

Please Log in to join the conversation.

Valentin
Offline

3 years 5 months ago #123510 by Valentin

Replied by Valentin on topic Find and replace datetime in MySql

Hi 7webcreator,

Do you have access to phpMyAdmin to manage this database? Updating could be easier if you directly edit the record with a few clicks.

Kind regards,
Valentín

Please Log in to join the conversation.

7webcreator
Topic Author
Offline

3 years 5 months ago #123544 by 7webcreator

Replied by 7webcreator on topic Find and replace datetime in MySql

Hi Valentin,

Yes I have access to phpMyAdmin, Version: 4.0.10.7, and I get 550 lines to correct. So I thought that a code like the one I used to correct the text could be useful in this case. But I don't know the syntax to use to find and replace datetime type field.

Kind regards

Please Log in to join the conversation.

Valentin
Offline

3 years 5 months ago - 3 years 5 months ago #123577 by Valentin

Replied by Valentin on topic Find and replace datetime in MySql

Hi 7webcreator,

I did a quick test in local. I figure out a syntax that works to catch records between two dates:

UPDATE table_name
SET field_name = "19-02-2016 09:30:00" WHERE (field_name >= "01-01-2015 00:00:00" AND field_name <= "2016-02-22 00:00:00")

"2016-02-22 00:00:00" represent today's date.

Check how the dates are structured in your database, and see if indeed they are datetime type.
For example:

19-02-2016 09:30:00
19/02/2016 09:30:00

Last edit: 3 years 5 months ago by Valentin.

Please Log in to join the conversation.

7webcreator
Topic Author
Offline

3 years 5 months ago #123593 by 7webcreator

Replied by 7webcreator on topic Find and replace datetime in MySql

Hi Valentin,

Many thanks for that. I tried that

UPDATE orders
SET OpDate = "2016-04-22 23:00:00" WHERE (OpDate >= "2016-04-19 23:00:00" AND OpDate <= "2016-04-20 23:00:00")

and it did work. However, My concern is what will happen if the dates within this range should not be changed?

Please Log in to join the conversation.

Valentin
Offline

3 years 5 months ago #123596 by Valentin

Replied by Valentin on topic Find and replace datetime in MySql

May you elaborate?

Please Log in to join the conversation.

7webcreator
Topic Author
Offline

3 years 5 months ago #123617 by 7webcreator

Replied by 7webcreator on topic Find and replace datetime in MySql

If I want to find and replace only the day 2016-04-19 23:00:00. In my table, I have entries which date is 20th and should not be amended.

Please Log in to join the conversation.

Valentin
Offline

3 years 5 months ago - 3 years 5 months ago #123624 by Valentin

Replied by Valentin on topic Find and replace datetime in MySql

UDPATE: Update the value on the query:

UPDATE orders
SET OpDate = "2016-04-22 23:00:00" WHERE (OpDate >= "2016-04-19 00:00:00" AND OpDate <= "2016-04-19 23:59:00")

Now, it will only catch records from that specific day from 00:00:00 to 23:59:00

Last edit: 3 years 5 months ago by Valentin.

The following user(s) said Thank You: 7webcreator

Please Log in to join the conversation.

7webcreator
Topic Author
Offline

3 years 5 months ago #123640 by 7webcreator

Replied by 7webcreator on topic Find and replace datetime in MySql

Yes logical. I should have thought about it. Many thanks for your great assistance.

Please Log in to join the conversation.

Valentin
Offline

3 years 5 months ago #123646 by Valentin

Replied by Valentin on topic Find and replace datetime in MySql

I'm happy to help! :)

Please Log in to join the conversation.

Join today and get access to 1,000's of books and videos. Learn WordPress, Drupal, Magento, Joomla and more! Sign up today!