PL SQL - Convert timestamp to datetime/date

Question

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

The error was: ORA-01830: date format picture ends before converting entire input string 01830. 00000 - "date format picture ends before converting entire input string"

The goal is to return something like

2017-07-91 23:14:00 (without the content after the dot).

Here's what the SCHEDULED_TIME (timestamp) looked like:

Solution 1

The problem in your attempt is the function TO_DATE() applied to a 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 attempts to convert this string to a date. Depending on your NLS_TIMESTAMP_FORMAT, you may get different errors.

The way to convert a timestamp to a date (datetime) - while truncating off the fractions of a second - is 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 that format, you can truncate the strings first and apply TO_DATE directly:

to_date(substr(scheduled_time, 1, 19), 'yyyy-mm-dd hh24:mi:ss')

Solution 2

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 review the docs to see the difference between to_timestamp and to_char.