Find and replace datetime in MySql
- 7webcreator
- Topic Author
- Offline
Less
More
- Posts: 34
- Thank you received: 0
3 years 9 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
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
Less
More
- Posts: 23621
- Thank you received: 695
3 years 9 months ago #123465
by Nick
Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining
Replied by Nick on topic Find and replace datetime in MySql
Hi 7webcreator,
Something like the following should work well:
If you want it between (inclusive) certain dates, you can use something similar to:
or:
Hope this helps! Let us know if you have any questions and we'll be glad to answer.
Kind regards,
Nick
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.
- 7webcreator
- Topic Author
- Offline
Less
More
- Posts: 34
- Thank you received: 0
3 years 9 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?
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
Less
More
- Posts: 23621
- Thank you received: 695
3 years 9 months ago #123496
by Nick
Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining
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
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.
- 7webcreator
- Topic Author
- Offline
Less
More
- Posts: 34
- Thank you received: 0
3 years 9 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
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
Less
More
- Posts: 9893
- Thank you received: 385
3 years 9 months ago #123510
by Valentin
Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining
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
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.
- 7webcreator
- Topic Author
- Offline
Less
More
- Posts: 34
- Thank you received: 0
3 years 9 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
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
Less
More
- Posts: 9893
- Thank you received: 385
3 years 9 months ago - 3 years 9 months ago #123577
by Valentin
Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining
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:
"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:
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
Last edit: 3 years 9 months ago by Valentin.
Please Log in to join the conversation.
- 7webcreator
- Topic Author
- Offline
Less
More
- Posts: 34
- Thank you received: 0
3 years 9 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?
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
Less
More
- Posts: 9893
- Thank you received: 385
3 years 9 months ago #123596
by Valentin
Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining
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.
- 7webcreator
- Topic Author
- Offline
Less
More
- Posts: 34
- Thank you received: 0
3 years 9 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
Less
More
- Posts: 9893
- Thank you received: 385
3 years 9 months ago - 3 years 9 months ago #123624
by Valentin
Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining
Replied by Valentin on topic Find and replace datetime in MySql
UDPATE: Update the value on the query:
Now, it will only catch records from that specific day from 00:00:00 to 23:59:00
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
Last edit: 3 years 9 months ago by Valentin.
The following user(s) said Thank You: 7webcreator
Please Log in to join the conversation.
- 7webcreator
- Topic Author
- Offline
Less
More
- Posts: 34
- Thank you received: 0
3 years 9 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
Less
More
- Posts: 9893
- Thank you received: 385
3 years 9 months ago #123646
by Valentin
Follow us on Twitter - twitter.com/OSTraining
Like us on Facebook - facebook.com/ostraining
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.