How to convert year month type to datetime format-CodePudding

I want to convert value year month ('202101') 2021 > year 01 > month

to 2021-01-31(end day) in toad for oracle

please help me, if somebody know.

CodePudding user response：

Try 'DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )' e.g. DATETIMEFROMPARTS ( 2021, 1 )

CodePudding user response：

You can make use of the function LAST_DAY

Test the code below on livesql.oracle.com (simply register and sign up, it is free)

CREATE TABLE test_period (
  yyyymm varchar(6)
);

INSERT INTO test_period (yyyymm) VALUES ('202101');
INSERT INTO test_period (yyyymm) VALUES ('202102');
INSERT INTO test_period (yyyymm) VALUES ('202103');
INSERT INTO test_period (yyyymm) VALUES ('202104');
INSERT INTO test_period (yyyymm) VALUES ('202105');
INSERT INTO test_period (yyyymm) VALUES ('202106');
INSERT INTO test_period (yyyymm) VALUES ('202107');
INSERT INTO test_period (yyyymm) VALUES ('202108');
INSERT INTO test_period (yyyymm) VALUES ('202109');
INSERT INTO test_period (yyyymm) VALUES ('202110');
INSERT INTO test_period (yyyymm) VALUES ('202111');
INSERT INTO test_period (yyyymm) VALUES ('202112');

SELECT TO_CHAR(LAST_DAY(TO_DATE(yyyymm||01,'yyyymmdd')),'yyyy-mm-dd')
FROM test_period;