SPECIAL OFFER: Only $59 for access to everything in OSTraining for 1 year! You save $85!  1
Join today and get access to 1,000's of books and videos. Learn WordPress, Drupal, Magento, Joomla and more! Sign up today!

Find and replace datetime in MySql

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.

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

Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining

Please Log in to join the conversation.

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.

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

Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining

Please Log in to join the conversation.

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.

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

Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining

Please Log in to join the conversation.

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.

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

Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining

Please Log in to join the conversation.

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.

3 years 5 months ago #123596 by Valentin
Replied by Valentin on topic Find and replace datetime in MySql
May you elaborate?

Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining

Please Log in to join the conversation.

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.

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

Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining
The following user(s) said Thank You: 7webcreator

Please Log in to join the conversation.

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.

3 years 5 months ago #123646 by Valentin
Replied by Valentin on topic Find and replace datetime in MySql
I'm happy to help! :)

Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining

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!