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