I'm very confused when I convert string to date, there is an error: ORA-01839: date not valid for month specified
Bur I'm pretty sure NO invalid date value in my table.
STRAT_YEAR STRAT_MONTH STRAT_DAY STRAT_TIME
2010 7 19 19:43:00
2010 7 19 21:22:00
2010 7 19 22:59:00
2010 7 20 00:34:00
2010 7 20 02:09:00
2010 7 20 13:42:00
2010 7 20 15:19:00
2010 7 20 16:56:00
2010 7 20 23:28:00
2010 7 21 01:02:00
Here is SQL to convert string to date
SELECT TO_DATE((TO_CHAR(LPAD(M.STRAT_DAY, 2, '0')) ||
TO_CHAR(LPAD(M.STRAT_MONTH, 2, '0')) ||
TO_CHAR(M.STRAT_YEAR) || M.STRAT_TIME),
'DDMMYYYYHH24:MI:SS')
FROM SETI_EVENTS_TEST M
My real table has 7000+ rows, and there is an error, But I'm pretty sure NO invalid date value in my table.
ORA-01839: date not valid for month specified
create table SETI_EVENTS_TEST
(
strat_year NUMBER,
strat_month NUMBER,
strat_day NUMBER,
strat_time CHAR(20)
)
insert into SETI_EVENTS_TEST (STRAT_YEAR, STRAT_MONTH, STRAT_DAY, STRAT_TIME)
values (2010, 7, 19, '19:43:00 ');
insert into SETI_EVENTS_TEST (STRAT_YEAR, STRAT_MONTH, STRAT_DAY, STRAT_TIME)
values (2010, 7, 19, '21:22:00 ');
insert into SETI_EVENTS_TEST (STRAT_YEAR, STRAT_MONTH, STRAT_DAY, STRAT_TIME)
values (2010, 7, 19, '22:59:00 ');
insert into SETI_EVENTS_TEST (STRAT_YEAR, STRAT_MONTH, STRAT_DAY, STRAT_TIME)
values (2010, 7, 20, '00:34:00 ');
insert into SETI_EVENTS_TEST (STRAT_YEAR, STRAT_MONTH, STRAT_DAY, STRAT_TIME)
values (2010, 7, 20, '02:09:00 ');
insert into SETI_EVENTS_TEST (STRAT_YEAR, STRAT_MONTH, STRAT_DAY, STRAT_TIME)
values (2010, 7, 20, '13:42:00 ');
insert into SETI_EVENTS_TEST (STRAT_YEAR, STRAT_MONTH, STRAT_DAY, STRAT_TIME)
values (2010, 7, 20, '15:19:00 ');
insert into SETI_EVENTS_TEST (STRAT_YEAR, STRAT_MONTH, STRAT_DAY, STRAT_TIME)
values (2010, 7, 20, '16:56:00 ');
insert into SETI_EVENTS_TEST (STRAT_YEAR, STRAT_MONTH, STRAT_DAY, STRAT_TIME)
values (2010, 7, 20, '23:28:00 ');
insert into SETI_EVENTS_TEST (STRAT_YEAR, STRAT_MONTH, STRAT_DAY, STRAT_TIME)
values (2010, 7, 21, '01:02:00 ');