Hi
Could you please help me understand why I'm seeing below error? How can I resolve this?
CREATE TABLE MI_DTWKHIST_TST (DATE_D DATE, ENTY_KEY NUMBER);
INSERT INTO MI_DTWKHIST_TST VALUES (TO_DATE('26-MAR-2000 04:01 28','DD-MON-YYYY HH:MI SS'), 101);
INSERT INTO MI_DTWKHIST_TST VALUES (TO_DATE('26-MAR-2000 05:01 28','DD-MON-YYYY HH:MI SS'), 102);
SELECT to_char(DATE_D, 'dd-mon-yyyy hh:mi ssam'),
FROM_TZ(TO_TIMESTAMP(DATE_D), 'Asia/Baku'),
ENTY_KEY
FROM MI_DTWKHIST_TST
WHERE enty_key = 101;
ORA-01878: specified field not found in datetime or interval
01878. 00000 - "specified field not found in datetime or interval"
*Cause: The specified field was not found in the datetime or interval.
*Action: Make sure that the specified field is in the datetime or interval.
But when I query for enty_key = 102, I do not get any error:
SELECT to_char(DATE_D, 'dd-mon-yyyy hh:mi ssam'),
FROM_TZ(TO_TIMESTAMP(DATE_D), 'Asia/Baku'),
ENTY_KEY
FROM MI_DTWKHIST_TST
WHERE enty_key = 102;
desired o/p:
TO_CHAR(DATE_D,'DD-MON-YYYYHH:M FROM_TZ(TO_TIMESTAMP(DATE_D),'ASIA/BAKU') ENTY_KEY
------------------------------- ---------------------------------------------------
26-mar-2000 05:01 28am 26-MAR-00 05.01.28.000000000 AM ASIA/BAKU 102
Using Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
Windows 7
Thanks..