Can TO_DATE function return a TIMESTAMP?

Question

Can, in any form, TO_DATE function return a TIMESTAMP? Example:

I have the following input: '2019-05-31 13:15:25.000000000' I want to have '2019-05-31 13:15:25' as a result using TO_DATE and not TO_TIMESTAMP.

I tried:

select to_date(substr('2019-05-31 13:15:25.000000000', 1, 19), 'YYYY-MM-DD HH24:MI:SS') from dual;

But it returns:

31/05/2019

Do you just want the time to display in your return output? Because it IS still there, it's just not being displayed. — kfinity, Jul 11 '19 at 15:32
If you got `'2019-05-31 13:15:25.000000000'` as a string, you can simply use the SUBSTR with TO_DATE. If it's a TIMESTAMP apply TO_CHAR. — dnoeth, Jul 11 '19 at 15:37
If you want to get rid of the fractional seconds of a timestamp `cast(timestampcol as date)` — dnoeth, Jul 11 '19 at 15:43
When I execute your test statement in PL/SQL Developer it returns `31-May-2019 13:15:25`. I have the default DATE format set to `DD-MMM-YYYY` and the default TIME format set to `HH:MM:SS`. — Bob Jarvis - Слава Україні, Jul 11 '19 at 16:17

score 2 · Accepted Answer · answered Jul 11 '19 at 15:27

2

to_date returns a date. If you need a timestamp, you should use to_timestamp.

answered Jul 11 '19 at 15:27

Mureinik

297,002
52
306
350

But an Oracle DATE still has a TIME part, just doesn't display by default :-) – dnoeth Jul 11 '19 at 15:35

score 1 · Answer 2 · answered Jul 11 '19 at 15:30

TO_DATE is used to read the date format you have as in your query it reads as YYYY-MM-DD HH24:MI:SS

     to_date(substr('2019-05-31 
    13:15:25.000000000', 1, 19), 'YYYY-. 
     MM-DD HH24:MI:SS') from dual

and gives the default date format not default timestamp format of your database in order to manipulate the date you can use TO_CHAR(to_date(substr('2019-05-31 13:15:25.000000000', 1, 19), 'YYYY-. MM-DD HH24:MI:SS'),"your_format") from dual

or use to_timestamp(...)

score 1 · Answer 3 · answered Jul 11 '19 at 16:00

If you just want to display a timestamp value as so, then keep style1, otherwise if you wish to convert to a truncated datetime type value, then use style2 as in this :

with t( ts ) as
(
 select systimestamp from dual
)
select to_char(ts,'YYYY-MM-DD HH24:MI:SS') as style1,
       to_timestamp(to_char(ts,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') as style2,
       ts as original
  from t;

STYLE1              STYLE2                           ORIGINAL
------------------- -------------------------------  -----------------------------------
2019-07-11 17:03:15 11-JUL-19 05.03.15.000000000 PM  11-JUL-19 05.03.15.298742 PM +01:00

score 0 · Answer 4 · answered Jul 11 '19 at 21:37

0

You convert between date and timestamp values using cast.

However I am not sure what your starting datatype is, or why you need to_date() specifically to return a timestamp.

answered Jul 11 '19 at 21:37

William Robertson

15,273
4
38
44

Can TO_DATE function return a TIMESTAMP?

4 Answers4