convert time

hello,

I have this data from an external company and after I import it into a table they send time stamps like this '2017-06-01T09:40:58.520+0000'

how can I convert it to 'DD-MON-YYYY HH24:MI:SS'

Many Thanks
mehul bhaktaAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
To convert that string to an Oracle Timestamp:
select to_timestamp_tz('2017-06-01T09:40:58.520+0000','YYYY-MM-DD"T"HH24:MI:SS.FF3TZHTZM') from dual;
0
 
ste5anSenior DeveloperCommented:
First of all: These are ISO 8601 date/time values.

Then: You don't convert them. Store it as the appropriate DATETIME data type.

Don't mix up data type and formatting (visual representation).
0
 
mehul bhaktaAuthor Commented:
ah problem is I need to convert them to the given format in order to use the column with other tables
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ste5anSenior DeveloperCommented:
Correct your data model. We store data according to the domain base type.
0
 
mehul bhaktaAuthor Commented:
that's the one @ slightwv (䄆 Netminder)

especially with cast(to_timestamp_tz(t.start_utc,'YYYY-MM-DD"T"HH24:MI:SS.FF3TZHTZM') as date)

thank you :)
0
 
mehul bhaktaAuthor Commented:
thank you for your help : )
0
 
slightwv (䄆 Netminder) Commented:
No problem. Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.