CODEWITHSUNDEEP
oracle-databasetimezone
user1751356
user1751356

Reputation: 615

timestamp conversion issue in oracle

I am trying to convert data from oracle date field which is in local eastern time to UTC format and I'm using below function to achieve it 

CAST("date field" AS TIMESTAMP) AT TIME ZONE 'UTC'

and my output looks like below

06-NOV-19 09.55.21.000000 AM

However i need to format the output to below format

11/6/2019 9:55:21.000000000 AM

Is there an oracle function which i directly use to do above format conversion?

Upvotes: 0

Views: 89

Answers (2)

Michael Broughton
Michael Broughton

Reputation: 4055

Formatting and the actual value are separate things. The value, you say is correct, however you want it to look in a different style than the default.

Look into TO_CHAR if you want a specifically formatted string output. eg:

SELECT TO_char(cast(sysdate as timestamp) at time zone 'UTC','mm/dd/yyyy hh:mi:ssxFF am') from dual;

11/18/2019 07:39:42.000000 pm

Upvotes: 1

Rodrigo Machado
Rodrigo Machado

Reputation: 107

I tried on my database and the same consult turn the expected result. Try to confer this web site that will show the config of date in oracle: http://blog.marvinsiq.com/2018/08/23/formatar-data-e-hora-no-oracle-sql-developer/

Upvotes: 0

Related Questions