CODEWITHSUNDEEP
sqloracle-database
Anna K
Anna K

Reputation: 1774

How to convert a VARCHAR to a date in ORACLE SQL?

I have that field in my table:

2020-01-16T10:55:16..296518000

How to convert this Varchar into a date in format 'YYYY-MM-DD' ?

I tried:

select TRUNC(to_date(DATE_UPDATED ,'YYYY-MM-DD hh24:mi:ss'))  from JOB_SCHEDULE_TBL

but I'm getting an error:

ORA-01830: date format picture ends before converting entire input string

Upvotes: 0

Views: 4364

Answers (2)

EdStevens
EdStevens

Reputation: 3872

You are confusing the format of DATE with what you are seeing on the screen. A DATE data type has no "format". It's Oracle's internal, binary format. Even when you SELECT TO_DATE ..., the result is going to get displayed by the client, and to do so the client will have to peform (under the covers) a TO_CHAR on the resulting DATE. And that implied to_char will use the current system/session/ settings for TNS_DATE_FORMAT.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Just use substr():

select to_date(SUBSTR(DATE_UPDATED, 1, 10) ,'YYYY-MM-DD')

The trunc() is unnecessary.

Upvotes: 2

Related Questions