1

I need to trim the last seven characters off of all the cells in a column.

This query returns the results that I need

SELECT test_date, SUBSTRING(test_date FROM 1 FOR CHAR_LENGTH(test_date) - 7) as test from results;

I now need to take the computed result from the substring and replace the original values.

Here's what the data looks like, if it helps.

Data Table

0

Make a simple Update

CREATE TABLE Table1
    (`test_date` datetime)
;
INSERT INTO Table1
    (`test_date`)
VALUES
    ('2020-06-10 00:06:17.185449'),
    ('2020-06-10 00:11:05.943217'),
    ('2020-06-10 00:13:45.375109'),
    ('2020-06-10 00:13:45.461321'),
    ('2020-06-10 00:15:32.497548'),
    ('2020-06-10 00:15:32.544873')
;
UPDATE Table1 SET test_date = SUBSTRING(test_date FROM 1 FOR CHAR_LENGTH(test_date) - 7)
SELECT * FROm Table1
| test_date           |
| :------------------ |
| 2020-06-10 00:00:00 |
| 2020-06-10 00:00:00 |
| 2020-06-10 00:00:00 |
| 2020-06-10 00:00:00 |
| 2020-06-10 00:00:00 |
| 2020-06-10 00:00:00 |

db<>fiddle here

  • Thanks! Of course it's that simple :/ – Chaostime Jun 27 '20 at 14:40
  • please accept it – nbk Jun 27 '20 at 14:45
  • Hey sorry! I thought I had, thanks for the prompt :) – Chaostime Jun 27 '20 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.