I want to remove millisecond from 21-02-14 10:41:08.000000000 PM
.
I try
select to_date('21-02-14 10:41:08.000000000 PM','DD/MM/YY HH:MI:SS:SSSSS PM') from dual;
error:
Error starting at line : 1 in command -
select to_date('21-02-14 10:41:08.000000000 PM','DD/MM/YY HH:MI:SS:PM') from dual
Error report -
SQL Error: ORA-01855: AM/A.M. or PM/P.M. required
01855. 00000 - "AM/A.M. or PM/P.M. required"
*Cause:
*Action:
You're specifying fractional seconds, but your format has "seconds past midnight" - which doesn't have 9 digits. You're also specifying a colon in your format string, when your data has a dot. Try:
select cast(to_timestamp('21-02-14 10:41:08.000000000 PM','DD-MM-YY HH:MI:SS.FF9 PM') as date) from dual;
See more on this question at Stackoverflow