Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

TO_CHAR() and TO_DATE() from TIMESTAMP WITH TIMEZONE

foxhoundApr 30 2019 — edited Apr 30 2019

Hey,

I have small problem with inconsistent results when using  TO_CHAR() and TO_DATE() from TIMESTAMP. My NLS are set as follows:

NLS_DATE_FORMAT 'MM/DD/YY HH24:MI:SS'

NLS_TIMESTAMP_FORMAT 'MM/DD/YY HH24:MI:SS.FF'

NLS_TIMESTAMP_TZ_FORMAT 'MM/DD/YY HH24:MI:SS.FF TZD'

the queries and their results:

SELECT CURRENT_DATE

FROM dual;

04/30/19 15:22:43

SELECT

TO_TIMESTAMP(CURRENT_DATE, 'MM/DD/YY HH24:MI:SS') AS timestamp

from dual;

04/30/19 15:22:43.000000000

SELECT

TO_CHAR

(

    TO_TIMESTAMP(CURRENT_DATE, 'MM/DD/YY HH24:MI:SS'),  'MM/DD/YY HH24:MI:SS'

)  AS to_char

FROM dual;

04/30/19 15:22:43

SELECT

TO_DATE

(

    TO_TIMESTAMP(CURRENT_DATE, 'MM/DD/YY HH24:MI:SS'),  'MM/DD/YY HH24:MI:SS'

)  AS to_date

FROM dual;

Error starting at line : 59 in command -

SELECT

TO_DATE

(

    TO_TIMESTAMP(CURRENT_DATE, 'MM/DD/YY HH24:MI:SS'),  'MM/DD/YY HH24:MI:SS'

)  AS to_date

FROM dual

Error report -

ORA-01830: date format picture ends before converting entire input string

Why TO_DATE() returns error when TO_CHAR() does not for same fmt and input data?

Message was edited by: foxhound, added SQL formatting

Comments
Post Details
Added on Apr 30 2019
23 comments
79,717 views