Dear Gurus, Here I'm posting 3 queries. The 1st and 3rd are working; while the 2nd one throws the error ORA-01835. Please help.
--(1) TO_DATE - 'MONTH' format element.
SELECT SYSDATE AS "Today",
TO_CHAR(SYSDATE, 'MONTH') AS "Current Month",
TO_DATE(TO_CHAR(SYSDATE, 'MONTH'), 'MONTH') AS "Applying TO_DATE on SYSDATE",
TO_DATE('JANUARY', 'MONTH') AS "Applying TO_DATE on a MONTH"
FROM DUAL;
/
Result :
Today Current M Applying TO_DATE on SYSDATE Applying TO_DATE on a MONTH
--------- --------- --------------------------- ---------------------------
12-APR-16 APRIL 01-APR-16 01-JAN-16
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--(2) TO_DATE - 'DAY' format element. (Query NOT working)
SELECT SYSDATE AS "Today",
TO_CHAR(SYSDATE, 'DAY') AS "DAY : TUESDAY",
TO_DATE(TO_CHAR(SYSDATE, 'DAY'), 'DAY') AS "Applying TO_DATE on SYSDATE",
TO_DATE('FRIDAY', 'DAY') AS "Applying TO_DATE on a DAY"
FROM DUAL;
/
--ORA-01835: day of week conflicts with Julian date
--01835. 00000 - "day of week conflicts with Julian date"
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--(3) TO_DATE - 'DAY' format element.
SELECT SYSDATE AS "Today",
TO_CHAR(SYSDATE, 'DAY') AS "DAY : TUESDAY",
--TO_DATE(TO_CHAR(SYSDATE, 'DAY'), 'DAY') AS "Applying TO_DATE on SYSDATE",
TO_DATE('FRIDAY', 'DAY') AS "Applying TO_DATE on a DAY"
FROM DUAL;
/
Result :
Today DAY : TUE Applying TO_DATE on a DAY
--------- --------- -------------------------
12-APR-16 TUESDAY 01-APR-16
Note : The same error ORA-01835 is appearing with other format elements such as DY,
Thanks in advance.