CODEWITHSUNDEEP
sqloracle-databasedatetimestampto-date
Haha
Haha

Reputation: 1009

Can TO_DATE function return a TIMESTAMP?

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

Upvotes: 1

Views: 18871

Answers (4)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

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

Upvotes: 3

William Robertson
William Robertson

Reputation: 16001

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.

Upvotes: 0

Himanshu
Himanshu

Reputation: 3970

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(...)

Upvotes: 1

Mureinik
Mureinik

Reputation: 311373

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

Upvotes: 3

Related Questions