Can I change the datatype and max length of a column in Table B that I'm creating with a CREATE TABLE AS SELECT FROM Table A?

Question

I would like to have the converted date formats to be in the ResultB Table instead of the CHAR type dates and I was looking for a way to do this inside the create table state in 1 go.

I have successfully created a new Table ResultB with a subset of data from source Table SourceA. There is a column UPLSTD (CHAR type) that contains dates, in order to compare with the current date I converted the CHAR type dates with a to_date function, this also works.

CREATE TABLE ResultB AS
            (SELECT UPUPRF,
                    UPTEXT,
                    UPLSTD
                    FROM SourceA
                    WHERE to_date(SUBSTR(DIGITS(UPLSTD), 27, 6), 'RRMMDD') < CURRENT_DATE - 10 DAYS)
            WITH DATA

I want the output to be:

UPUPRF    UPTEXT    UPLSTD (DATE Format)
------   --------   --------------------
ABCD      ABCDEFG   2019-08-21

But the actual is:

UPUPRF    UPTEXT    UPLSTD (CHAR Format)
------   --------   --------------------
ABCD      ABCDEFG   190821

Answer 1

Here's an expressions you can use to convert your CHAR field to a true DATE format.

DATE(TO_DATE(ACHARDATE, 'YYMMDD'))

Hope that helps!