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

New contributor
Chaostime is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
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

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

Your Answer

Chaostime is a new contributor. Be nice, and check out our Code of Conduct.

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.