PL SQL - convert timestamp to datetime
select
to_timestamp(SCHEDULED_TIME,'YYYY-MM-DD HH24:MI:SS.FF') as SCHEDULED_TIME,
TRUNC(to_date(to_timestamp(SCHEDULED_TIME,'YYYY-MM-DD HH24:MI:SS.FF'),'YYYY-MM-DD HH24:MI:SS'))
from S_TIDAL_STATUS
Error: ORA-01830: date format image ends before converting entire input string 01830. 00000 - "date format ends before converting entire input string"
The goal is to return something like
2017-07-91 23:14:00 (no content after period).
This is what SCHEDULED_TIME looked like (timestamp):
source to share
The problem in your attempt is the TO_DATE () function being applied to the timestamp. TO_DATE () takes a VARCHAR2 (string) input, not a timestamp. So Oracle converts the timestamp to a string first, implicitly using your NLS_TIMESTAMP_FORMAT parameter, and then tries to convert that string to a date. Depending on your NLS_TIMESTAMP_FORMAT, you might get different errors.
A way to convert a timestamp to a date (datetime) - when truncating fractions of a second - with the CAST function. Example:
select systimestamp,
cast (systimestamp as date) as ts_cast_to_date
from dual
;
Alternatively, if all your strings are EXACTLY in this format, you can trim the strings first and apply TO_DATE directly:
to_date(substr(scheduled_time, 1, 19), 'yyyy-mm-dd hh24:mi:ss')
source to share
This should do the trick:
select
to_char(SCHEDULED_TIME,'YYYY-MM-DD HH24:MI:SS.FF') as time_to_csecs,
to_char(SCHEDULED_TIME,'YYYY-MM-DD HH24:MI:SS') as time_to_secs,
TRUNC(to_date(to_char(SCHEDULED_TIME,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')) as time_to_day
from S_TIDAL_STATUS
Please see the docs to see the difference between to_timestamp and to_char.
source to share