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:

Hi, have you tried **to_char(SCHEDULED_TIME,'YYYY-MM-DD HH24:MI:SS')**? — hmmftg, Jul 08 '17 at 06:10
You don't need PL/SQL for this. Looks like a plain SQL question. — William Robertson, Jul 08 '17 at 07:44

score 6 · Answer 1 · answered Jul 07 '17 at 20:53

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')

Jeff Holt · Answer 2 · 2017-07-07T20:16:40.850

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.

edited Jul 07 '17 at 20:16

answered Jul 07 '17 at 20:11

Jeff Holt

2,940
3
22
29

hmm what's the reason that it has to be converted to string first? just curious – Anna Huang Jul 07 '17 at 20:22
@AnnaHuang If you don't convert the timestamp to a string, then sqldeveloper will display a string based upon the current setting of NLS_TIMESTAMP_FORMAT (for timestamp) or NLS_DATE_FORMAT (for date). Essentially, sqldeveloper (and sqlplus and just about any other adhoc app) will use the Oracle API of choice to coerce all datatypes to strings by telling the API to do the conversion. In other words, the app is telling Oracle to give back strings instead of values in their native (db) data type. It makes it easier for folks to write apps like sqldeveloper and sqlplus. – Jeff Holt Jul 07 '17 at 20:25
@jeff6times7 - actually it is the Oracle database engine itself that will do the implicit conversions, not the front-end (SQL Developer or SQL\*Plus). But otherwise the explanation is correct. – Jul 07 '17 at 21:22
@mathguy That is correct but the app has to tell the API which datatypes it wants. Then Oracle obliges if it can. – Jeff Holt Jul 07 '17 at 23:01

PL SQL - Convert timestamp to datetime/date

2 Answers2