CODEWITHSUNDEEP
oracle-database
TA01
TA01

Reputation: 19

How to convert date format from "28-03-18 09:11:34.000000000 PM" to "MM-DD-YYYY HH24:MI:SS" in oracle

I am trying to convert the date for one reporting project where I've got stuck with below:

select 
UPDATED_DT, to_CHAR(UPDATED_DT,'MM-DD-YYYY HH24:MI:SS'), TO_DATE(to_CHAR(UPDATED_DT,'MM-DD-YYYY HH24:MI:SS'),'MM-DD-YYYY HH24:MI:SS')
from COMPANY;

Result:

28-03-18 09:11:34.000000000 PM          03-28-2018 21:11:34           28-03-2018 21:11:34

If you observe i am getting correct format with TO_CHAR, however i want the date in date data type. how can i achieve "MM-DD-YYYY HH24:MI:SS"?

Upvotes: 0

Views: 250

Answers (1)

Beefstu
Beefstu

Reputation: 857

Use this to set the format you want

ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

Upvotes: 0

Related Questions