CODEWITHSUNDEEP
oracle-databaseoracle11goracle10gsql-loader
Sunitha
Sunitha

Reputation: 135

Date format to change

I need help to change the date format from (05 May 2016 12:00 AM) to (YYYY-MM-DD HH:MM:SS) format.

Can you please help me on this?

Many thanks for your help.

I have tried below format

SELECT TO_CHAR(TO_DATE('2 Apr 2015 12:00 AM', 'DD-MON-YYYY '), 'YYYY-MM-DD')
FROM dual;

Thanks for the Help MTO. But i am using same format in sqlldr then getting below error.

ora-01821 date format not recognized

 LAST_UPDATE DATE 'TO_CHAR(TO_DATE(:LAST_UPDATE, 'DD MON YYYY HH12:MI AM'),'YYYY-MM-DD HH24:MI:SS')',

Upvotes: 0

Views: 912

Answers (2)

Alex Poole
Alex Poole

Reputation: 191435

If you're doing this in SQL*Loader and populating a DATE column then you want your initial string to be converted to a date, not back to another string in another format.

If you use the SQL operator functionality then you only need the inner to_date() part, not the outer to_char(), and notice that the operator is enclosed in double quotes rather than single quotes:

LAST_UPDATE "TO_DATE(:LAST_UPDATE, 'DD MON YYYY HH12:MI AM')",

But there is simpler handling for datetimes and intervals, which you are sort of trying to use with the DATE keyword; but then you're supplying the SQL operators instead of just a format mask. You can just do:

LAST_UPDATE DATE 'DD MON YYYY HH12:MI AM'.

This assumes SQL*Loader is run in an english-language NLS_LANG environment, since it relies on the NLS_DATE_LANGUAGE setting to handle the MON element. If that is not the case then the SQL operator approach can be used, with the optional third argument to to_date() to specify that it is expecting the string to be in English.

Your question seems to be partly confused by thinking that Oracle stores dates with a specific format. It does not; it uses an internal representation which you generally don't need to know about, and it's up to your client to display that internal value in a readable format, which is does using explicit or implicit conversions. You seem to be assuming dates are 'stored' as YYYY-MM-DD HH24:MI:SS, but that is just how your client is displaying the data to you.

Upvotes: 1

MT0
MT0

Reputation: 168361

Use the full format mask:

SELECT TO_CHAR(
         TO_DATE( '2 Apr 2015 12:00 AM', 'DD MON YYYY HH12:MI AM'),
         'YYYY-MM-DD HH24:MI:SS'
       )
FROM dual;

Upvotes: 2

Related Questions