CODEWITHSUNDEEP
oracle-databasedate
Bagzli
Bagzli

Reputation: 6579

Convert UTC date to oracle Date

I have a string in this format: "2013-06-05T19:41:12.739" and I need to convert it to a date field in this format: "2013-06-05 19:41:12"

How can I do this with Oracle?

Upvotes: 1

Views: 3037

Answers (1)

user330315
user330315

Reputation:

You use to_date() or to_timestamp() to convert a string literal to a date/timestamp value:

If you need the milliseconds you have to convert it into a timestamp, otherwise (if you want to discard the milliseconds) you can convert it into a date:

select to_timestamp('2013-06-05T19:41:12.739', 'yyyy-mm-dd"T"hh24:mi:ss.ff3')
from dual;

To get rid of the milliseconds, simply cast the result from the above statement to a DATE

select cast(to_timestamp('2013-06-05T19:41:12.739', 'yyyy-mm-dd"T"hh24:mi:ss.ff3') as date)
from dual;

I need to convert it to a date field in this format:

A DATE column does NOT have a "format".

You apply a format to a DATE column when you display it. Either explicitely by using to_char() or implicitely by the NLS settings in effect (or by some code in your application).

Upvotes: 2

Related Questions