convert column data varchar to timestamp in H2 Database

user_13963304 0
Viewed 2831

I wanted to convert varchar data into timestamp in existing table. And also change datatype of table.

DB: H2
platform: Windows
h2
1 Answers

Unfortunately, your timestamp is not in SQL or ISO format, so you can't simply change data type of a column. Automatic conversion is not possible in your case.

In 1.4.200 and older versions you need to perform two steps:

CREATE TABLE TEST(V VARCHAR);
INSERT INTO TEST VALUES ('7-18-2020 7:51');

UPDATE TEST SET V = PARSEDATETIME(V, 'M-d-yyyy HH:mm');
ALTER TABLE TEST ALTER COLUMN V SET DATA TYPE TIMESTAMP;

PARSEDATETIME returns a TIMESTAMP value, but here it is assigned back to VARCHAR column, this operation cause an implicit cast using SQL format ('2020-07-18 07:51:00') that can be converted back to TIMESTAMP automatically during the following change of data type of the column.

In H2 2.0.202 and newer versions there is a simpler one-step solution:

CREATE TABLE TEST(V VARCHAR);
INSERT INTO TEST VALUES ('7-18-2020 7:51');

ALTER TABLE TEST ALTER COLUMN V
    SET DATA TYPE TIMESTAMP USING PARSEDATETIME(V, 'M-d-yyyy HH:mm');
user_117319870
Related