Date format to change

0
votes

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')',
2
oracleoracle11goracle10gsql-loader
Just do it. Whats the issue? It will be (2016-05-05 00:00:00)... Whats the problem in that? - usamazf
The display format is controlled by the application displaying the values. A date value is stored without any format. Check the manual for your SQL client to see how you can change the output format - a_horse_with_no_name
hi i have tried but getting below error..ORA-01830: date format picture ends before converting entire input string - Sunitha
to_char(to_date(..)..) does not make any sense. You can simply replace your expression with a string constant '2015-04-02' - a_horse_with_no_name
Hmm, you writing '05 May..' and '2 Apr..' at the same post. - galakt

2 Answers

2
votes

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;
1
votes

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.